Oracle SQL – How to Total Additional Results from Alias Column Results

oracle

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.

enter image description here

Best Answer

This is simply GROUP BY on the first column:

SELECT INVOICE_DUE_DATE - INVOICE_DATE AS "Difference in Days",
     COUNT(INVOICE_DUE_DATE - INVOICE_DATE) AS "Number of Instances"
FROM customer.invoices
GROUP BY INVOICE_DUE_DATE - INVOICE_DATE;
Related Question