I'm attempting to display the same number of invoices that have the same number of days between two specific dates (e.g. issuance date and payment date). I can ascertain a segment of the aforementioned with the following query:
SELECT INVOICE_DUE_DATE - INVOICE_DATE AS "Difference in Days" FROM customer.invoices;
which displays the number days between those specified column-dates, but to clarify, I would like to create an additional column displaying how many instances occur between the number of days calculated from the above, preferably as another alias column- say "Number of Instances". I suspect the solution might involve the ROLLUP
operator or perhaps the CUBE
operator but I'm unable to effect. Here is my last unsuccessful attempt:
SELECT INVOICE_DUE_DATE - INVOICE_DATE AS "Difference in Days",
COUNT(INVOICE_DUE_DATE - INVOICE_DATE) AS "Number of Instances"
FROM customer.invoices;
I'm also open to alternatives and I'd appreciate any insight with respect syntax and structure as well. Thank you!
UPDATE
As requested, I've included a below screen-shot of the first query cited; so, in addition to the column below, I would like to create another alias column as "Number of Instances" counting the number of instances. Thank you.
GROUP BY
and potentially atrunc
. Or are you asking for the number of rows between aninvoice_due_date
of x and aninvoice_date
of y (in which case we'd need to understand what you mean by "between").