SQL Subquery and summarize data-CodePudding

I have this table: [1]: https://i.stack.imgur.com/Z4jbh.png

I need to know the: Number of invoices per customer and how many customers in total

I calculated how many invoices per customer but I stucked here:

select * from
(
select CustomerID,count(distinct InvoiceID) as 'Total Invoices'
from exam
GROUP BY CustomerID
) a

I need to see this kind of output:

Row 1: Customers 2 Invoices 2

Row 2: Customers 24 Invoices 1

Thanks for your advise

CodePudding user response：

You first need to think about how you want your output to look. For instance you want invoices per customer and this is a row per customer Now you want number of customers so this is one row Do you want three columns being Number of Customers, Customer ID, Number of Invoices

Then each line has the number of customers repeated

CodePudding user response：

Now you've explained the output you want, which is number of customer with n number of invoices the query is something like this

SELECT count(1) "customers",invoices from
 (SELECT customerID, count(1) "invoices"
   from exam
   group by customerID) inv_count group by invoices;

Slight update, I didn't look at your jpg, the invoices aren't distinct because of product:

SELECT count(1) "customers",invoices from
 (SELECT customer, count(distinct invoiceID) "invoices"
   from test
   group by customer) inv_count group by invoices;