Creating a 'Total' column

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi,

I'm querying two tables: Orders and Salesmen. I need to find out the
Salesmen monthly output so I do it with the following working SQL statement:



SUM(CASE MONTH(ORD_DATE) WHEN 1 THEN 1 ELSE 0 END) AS Jan, SUM(CASE
MONTH(ORD_DATE) WHEN 2 THEN 1 ELSE 0 END) AS Feb, SUM(CASE MONTH(ORD_DATE)
WHEN 3 THEN 1 ELSE 0 END) AS Mar

...etc.



The query nicely sums the orders by Salesman by month. However, I would like
now the query to sum the Salesmen's total orders in a new column called say,
Totals so that the row will have the total or sum of all months as:



Salesman_ID Jan Feb Mar. Total

14 1 3 2 6



Can it be done?

TIA

Mike

MSA03, SQL2k
 
Try a simple Sum() or count.

SUM(CASE MONTH(ORD_DATE) WHEN 1 THEN 1 ELSE 0 END) AS Jan,
SUM(CASE MONTH(ORD_DATE) WHEN 2 THEN 1 ELSE 0 END) AS Feb,
SUM(CASE MONTH(ORD_DATE) WHEN 3 THEN 1 ELSE 0 END) AS Mar,
SUM(1) as AllMonths,...
 
It doesn't sum the prior sums. It just sums on its own. Consider counting
ballots for favorite day of the week. As each ballot is handled a tick mark
is place under the column for total votes cast as well as under the column
for a particular day.
 
Back
Top