Search code examples
sqlsumsubtotal

How to create subtotal in SQL with data from other table

I'm trying to create subtotals for sales in different zip codes, since the invoice do not have zip codes I need to get it from the customer table.

Here is my sql that will show all the transactions:

select c.name, c.zip, i.total, i.salestax from customer c, invoice i
where i.sdate >= '2019-09-01' and i.sdate <= '2019-09-30' and 
c.accountnum=i.customernr
order by c.zip

I tried to add SUM to total and salestax, but if I do that I get an SQL error.

Any help would be appreciated.

Thanks, KHJ

Solution

  • Here's your query, you can use sum() and group-by to get your result

    select c.name, c.zip, sum(i.total) as total, sum(i.salestax) as tax
from  invoice i
inner join customer c on c.accountnum=i.customernr
group by c.zip, c.name
where i.sdate >= '2019-09-01' and i.sdate <= '2019-09-30' 
order by c.zip