Prev: Need help on how to write this report.
Next: select rowcount per group before value in group = null
From: Jim in Arizona on 22 Jul 2008 11:29
I'm not sure how I could get a total from multiple results. For example,
this SQL:

SELECT COUNT(id) AS [Total Dialup Customers]
,rgheader.rg_name AS [Rate Group]
FROM customer
INNER JOIN custrate
ON custrate.custid = customer.id
INNER JOIN rgheader
ON rgheader.rg_id = custrate.rgid
WHERE rgheader.rg_id IN
(1,6,16,34,41,42,43,72,89)
AND NOT customer.active = 'N'
AND customer.storeid = 4
GROUP BY rgheader.rg_name

returns this result:

Total Dialup Customers Rate Group
---------------------------- --------------------------------------
4 Dialup Service - Unlimited Monthly
4 Dialup Service - Unlimited Yearly


How could I modify the SQL above to get the sum of the two results (4+4) so
I could have something like:

Total Dialup Customers Rate Group
---------------------------- --------------------------------------
4 Dialup Service - Unlimited Monthly
4 Dialup Service - Unlimited Yearly

TOTAL: 8

I realize that I could just omit the ",rgheader.rg_name AS [Rate Group]" and
"GROUP BY rgheader.rg_name" lines, which would result in:

Total Dialup Customers
-------------------------
8

I'm just wondering if there's an easier way of doing it rather than running
the same SQL twice.

Could the SUM function be used, like:

SELECT
SUM
(
COUNT(id) AS [Total Dialup Customers]
,rgheader.rg_name AS [Rate Group]
FROM customer
INNER JOIN custrate
ON custrate.custid = customer.id
INNER JOIN rgheader
ON rgheader.rg_id = custrate.rgid
WHERE rgheader.rg_id IN
(1,6,16,34,41,42,43,72,89)
AND NOT customer.active = 'N'
AND customer.storeid = 4
GROUP BY rgheader.rg_name
) AS [SUM TOTAL]

I know this doesn't work but how could I make it work?

TIA,
Jim


From: Plamen Ratchev on 22 Jul 2008 11:40
Add WITH ROLLUP after the GROUP BY clause and it will add the summary row.
In the SELECT list you can use the GROUPING function to check if the row has
been added by ROLLUP and replace NULL with 'Total' or something else.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

 | 
Pages: 1
Prev: Need help on how to write this report.
Next: select rowcount per group before value in group = null