How do I use multiple SUM in my query?

[Saudimac]

The (reduced) table structure is as follows:
Company Address Prov Item Qty ItemCost Total Month
------------------------------------------------------------------
ABC 1 First ON A101 2 10.50 21.00 May
ABC 1 First ON B321 2 2.00 4.00 May
ABC 1 First ON Z372 3 5.00 15.00 Jun
ABC 1 First ON Z372 1 5.00 5.00 Jul
ABC 1 First ON y563 2 1.00 2.00 Jul
.
.
.

The table has multiple companies in it though. The output that I'm looking for shows the company info, total for each month and the grand total. Ex:

Company Address Prov MayTotal JunTotal JulTotal GrandTotal
--------------------------------------------------------------------
ABC 1 First ON 25.00 15.00 7.00 47.00
.
.
.
Thanks in advance!

 

[PHV]

Have you tried to follow the CrossTab query wizard ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rac2]

The GrandTotal by Company is straightforward with a GROUP BY query.

SELECT Company, SUM(Total) AS GrandTotal
FROM MyTable
GROUP BY Company

For the monthly totals add expressions that sum the rows for a given month and sum zero for other months.

SELECT Company, SUM(Total) AS GrandTotal,
       SUM(IIf(Month="June", Total, 0)) AS JunTot
       
FROM MyTable
GROUP BY Company