Creating a 'Total' column

[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

 

[Duane Hookom]

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,...

 

[Mike]

Thank you much, it works. But how does Sum(1) exactly sum the prior sums?

 

[Duane Hookom]

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.