0

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

  • I'm not sure that I understand exactly what you're trying to accomplish. Some sample data and expected results might be quite illustrative. Are you trying to determine the number of invoices where the difference between the two dates is 1, the number of invoices where the difference between the two dates is 2, etc.? In which case you just need a GROUP BY and potentially a trunc. Or are you asking for the number of rows between an invoice_due_date of x and an invoice_date of y (in which case we'd need to understand what you mean by "between"). – Justin Cave Mar 3 '16 at 19:47
  • @JustinCave, I basically trying to display two alias-columns, one displaying the difference of days between two columns in the table, and then another alias column displaying the number of instances with the same number days between the previously mentioned dates. Does that make sense? – alexanderjsingleton Mar 3 '16 at 20:01
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.