Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Subtotals and total keeping the details

Tags:

mysql

I have a table with invoices:

invoice_num, customer_ID, usd
1              A          15.2
2              B           3.6
3              A         105.1
4              C           6.0

I need a report showing all the records (invoices) and adding a subtotal per customer. I know how to do it if I just show the total per customer (with GROUP BY customer_ID and WITH ROLLUP) but I need to keep the details, so I can't group the lines. The desired output is:

invoice_num  customer_ID   usd
1              A          15.2
3              A         105.1
Total customer A         120.3
2              B           3.6
Total customer B           3.6
4              C           6.0
Total customer C           6.0
Total customers          129.9

Thanks,

like image 633
jm_ Avatar asked Feb 09 '13 10:02

jm_


People also ask

What's the difference between subtotal and total?

Total is used to describe the final, overall sum of the other sets of numbers or subtotals. in contrast, subtotal describes the total of one set of numbers that will later be added to another set. Subtotals are used to show a calculation that forms part of a larger total sum.

What is the purpose of a subtotal?

Why do we need to use SUBTOTALS? Sometimes, we need data based on different categories. SUBTOTALS help us to get the totals of several columns of data broken down into various categories.


1 Answers

Also group on invoice_num:

SELECT   invoice_num, customer_ID, SUM(usd)
FROM     my_table
GROUP BY customer_ID, invoice_num WITH ROLLUP

See it on sqlfiddle.

like image 95
eggyal Avatar answered Sep 28 '22 00:09

eggyal