Complex Query - Part II

I am passing the following query:
select type, count(*) as 'Total', sum(case when status = 'Closed' then 1
else 0 end) as 'Closed',
sum(case when status = 'Pending' then 1 else 0 end) as 'Pending',
sum(case when status = 'Escalated' then 1 else 0 end) as 'Escalated'
from ticket where location in (select location from location where
lstate = 'mp')
and received_date between '5/1/2004' and '5/10/2004' group by type
My requirement is for totals of each column generated, suggest. I am
using an automated asp script written by me for generating dynamic sql
reports, for which i just need to give the sql statement, suggest how to
get totals of each column generated automatically if it is of numeric
value and to ignore totals if it is of non numeric value.
Through sql query only.
Part II because my earlier post on May 10, 2004 was not answered.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
On Wed, 16 Jun 2004 23:03:50 -0700, Preet Kanwaljit Singh Shergill wrote:

>
>I am passing the following query:
>select type, count(*) as 'Total', sum(case when status = 'Closed' then 1
>else 0 end) as 'Closed',
>sum(case when status = 'Pending' then 1 else 0 end) as 'Pending',
>sum(case when status = 'Escalated' then 1 else 0 end) as 'Escalated'
>from ticket where location in (select location from location where
>lstate = 'mp')
>and received_date between '5/1/2004' and '5/10/2004' group by type
>My requirement is for totals of each column generated, suggest. I am
>using an automated asp script written by me for generating dynamic sql
>reports, for which i just need to give the sql statement, suggest how to
>get totals of each column generated automatically if it is of numeric
>value and to ignore totals if it is of non numeric value.
>Through sql query only.
>
>Part II because my earlier post on May 10, 2004 was not answered.
>*** Sent via Devdex http://www.devdex.com ***
>Don't just participate in USENET...get rewarded for it!
Hi Preet,
If I understand your question correctly, you can either use this:
SELECT type, COUNT(*) AS 'Total',
COUNT(CASE WHEN status = 'Closed' THEN 1 END) AS 'Closed',
COUNT(CASE WHEN status = 'Pending' THEN 1 END) AS 'Pending',
COUNT(CASE WHEN status = 'Escalated' THEN 1 END) AS 'Escalated'
FROM ticket
WHERE location IN (SELECT location FROM location WHERE lstate = 'mp')
AND received_date BETWEEN '20040105' AND '20051005'
GROUP BY type
UNION ALL
SELECT 'Total', COUNT(*) AS 'Total',
COUNT(CASE WHEN status = 'Closed' THEN 1 END) AS 'Closed',
COUNT(CASE WHEN status = 'Pending' THEN 1 END) AS 'Pending',
COUNT(CASE WHEN status = 'Escalated' THEN 1 END) AS 'Escalated'
FROM ticket
WHERE location IN (SELECT location FROM location WHERE lstate = 'mp')
AND received_date BETWEEN '20040105' AND '20051005'
(untested)
Or you can look up ROLLUP in Books Online and see if that helps.
Final notes:
1) I changed the ambiguous date format you used to the recommended
YYYYMMDD format. Check if I didn't exchange the DD and MM parts of your
query.
2) I *think* that the subquery for location can be changed to an inner
join, but I'd have to know your table structure to be sure. The inner join
might yield better performance.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks a ton, it does solve my problem.
With this solution my SQL based reporting will be even faster than
before with minimal network traffic.
I no longer have to bother about html lipstick work.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!