Do lots of COUNTs

Hello :)

I seem to have somehow got myself into a situation where I'm having to run the following SELECTs, one after another, on a single ASP page. This is not tidy. How can I join them all together so I get a single recordset returned with all my stats in different columns?

SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years'
SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL
SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy'
SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULLsome ddl and sample data would help...read the hint sticky at the top of the forum...but I'll give it a shot

bit, would you like a result set of many rows or a single row

Also, why don't you use a sproc?|||Apologies:

CREATE TABLE [dbo].[tblQuiz] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[q1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q2] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q3] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q4] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q5] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q6] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q7] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q8] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[quizdate] [datetime] NULL ,
[ipaddress] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sessionid] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[score] [int] NULL
)

Sample data attached.

I'd like the results as a single row with 6 columns: one for each of the queries.

I'm not using a sproc because I'm lazy and haven't got round to taking it out of my ASP and putting it into one yet. And I don't know how to put all those SQL queries into one proc.|||Something like

select
sum(case when(id like'123%')then 1 else 0 end) Count1
,sum(case when([name] like'sys%')then 1 else 0 end) Count2
from sysobjects|||Do you want something like this....BTW I am not sure...

CREATE PROCEDURE CountTot

AS

SELECT
(SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years') as Totq3,
(SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL) as Totq4,
(SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy')as Totq5,
(SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes') as Totq6,
(SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes') as ToTq7,
(SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULL) as Totq8
FROM tblQuiz|||This will scan the table only once
SELECT
sum(case when q3='5 years +' OR q3='2 - 4 years' then 1 else 0 end) as Totq3
,sum(case when q4<>'' AND q4 IS NOT NULL then 1 else 0 end) as Totq4
,sum(case when q5='Unhappy' then 1 else 0 end) as Totq5
,sum(case when q6='Yes' then 1 else 0 end) as Totq6
,sum(case when q7='Yes' then 1 else 0 end) as ToTq7
,sum(case when q8<>'' AND q8 IS NOT NULL then 1 else 0 end) as Totq8
FROM tblQuiz|||This will scan the table only once
SELECT
sum(case when q3='5 years +' OR q3='2 - 4 years' then 1 else 0 end) as Totq3
,sum(case when q4<>'' AND q4 IS NOT NULL then 1 else 0 end) as Totq4
,sum(case when q5='Unhappy' then 1 else 0 end) as Totq5
,sum(case when q6='Yes' then 1 else 0 end) as Totq6
,sum(case when q7='Yes' then 1 else 0 end) as ToTq7
,sum(case when q8<>'' AND q8 IS NOT NULL then 1 else 0 end) as Totq8
FROM tblQuiz

That's exactly what I want, thankyou :) Now to try and figure out how it works :confused: :D|||Was that not equivalent to what I had posted ? :(|||Was that not equivalent to what I had posted ? :(nope, quite different :)

Dividing counts

I'm sorry if someone has already posted this but I've looked through a few pages to see if someone had already posted and I couldn't find anything. Anyways, I have two counts and I would like to divide them to get a percentage. Basically I would like to see a percentage of how many tickets are overdue. Here's my SQL:

select count(*)[No. of Tickets Overdue],
case
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
end [Ticket Status]
from whd.priority_type pt
inner join whd.job_ticket j on pt.priority_type_id = j.priority_type_id
where j.status_type_id = '1' and j.deleted = '0' and not j.priority_type_id = '5' and not j.priority_type_id = '6'
group by pt.due_hours
order by pt.due_hours desc
COMPUTE SUM(count(*))

select count(*)[Count2]
from whd.job_ticket jt
where jt.status_type_id = '1' and jt.deleted = '0'-- and not jt.priority_type_id = '5' and not jt.priority_type_id = '6'

--COMPUTE [No. of Tickets Overdue]/[Count2]

I know this isn't correct but basically the commented line at the bottom is what I want to do. I've only been doing SQL statements for a few months now, so I know its novice but any help is appreciated.

Thanks in advance.

We need more info here and better if you can post DDL, sample data (insert statements) and expected result. The help should be mutual, shouldn't it?

Code Snippet

;with cnt1

as

(

select

pt.due_hours,

count(*)[No. of Tickets Overdue],

case
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
end [Ticket Status]

from

whd.priority_type pt
inner join

whd.job_ticket j

on pt.priority_type_id = j.priority_type_id

where

j.status_type_id = '1'

and j.deleted = '0'

and not j.priority_type_id = '5'

and not j.priority_type_id = '6'

group by

pt.due_hours

),

cnt2

as

(

select count(*) as [Count2]
from whd.job_ticket jt
where jt.status_type_id = '1' and jt.deleted = '0'

)

select

a.[No. of Tickets Overdue],

a.[No. of Tickets Overdue] * 1.,

b.Count2,

nullif(b.Count2, 0)

from

cnt1 as a cross join cnt2 as b

order by

a.due_hours desc

COMPUTE SUM(a.[No. of Tickets Overdue]), sum(a.[No. of Tickets Overdue] * 1. / nullif(b.Count2, 0))

AMB

|||

You may have to modify this somewhat, I'm not exactly sure what the pt.due_hours is representing...

Code Snippet

select

(

select

count(*)

from

whd.priority_type pt

inner join

whd.job_ticket j

on

pt.priority_type_id=j.priority_type_id

where

j.status_type='1'

and j.deleted='0'

and j.priority_type_id not in ('5','6')

and datediff(hh,getDate(),j.report_date) >= pt.due_hours

)

/

(

select

count(*)

from

whd.priority_type pt

inner join

whd.job_ticket j

on

pt.priority_type_id=j.priority_type_id

where

j.status_type='1'

and j.deleted='0'

and j.priority_type_id not in ('5','6')

) 'PercentOverdue'

|||Thanks for the reply, and I actually had more with that case statement but I realized I didn't need it and I forgot to consolidate the case into a datediff. Anyways, well I like what you did with the SQL but I get a big fat '0' for my answer which I know isn't right. pt.due_hours is a field in the database that our ticketing system looks at and if the ticket has been open longer than the due hours than it is overdue. I'm trying to get a percentage based on how many tickets are overdue compared to overall open tickets. I tried multiplying the first select statement by 1. but still same result with a decimal and about 8 zeros. Thanks for the help.|||

Got it! Thanks Anthony Martin! Heres the code I just had to add *1. in front of the / and that worked. So, sorry but here's another question how do I round to the .00 instead of 12 numbers after the decimal point. Thanks again and thanks to hunchback I would've never figured out the *1. if I hadn't looked at your example. Thanks.

select
(select count(datediff(hh,j.report_date,getdate()))
from whd.job_ticket j
inner join whd.priority_type pt on pt.priority_type_id = j.priority_type_id
where j.status_type_id = '1' and j.deleted = '0' and j.priority_type_id not in('5','6')and datediff(hh,j.report_date, getdate()) >= pt.due_hours
)*1.
/
(
select count(*)[Count2]
from whd.job_ticket j
where j.status_type_id = '1' and j.deleted = '0'
)'% Overdue'

|||

No problem, you could cast the entire thing like this...

select
cast((select count(datediff(hh,j.report_date,getdate()))
from whd.job_ticket j
inner join whd.priority_type pt on pt.priority_type_id = j.priority_type_id
where j.status_type_id = '1' and j.deleted = '0' and j.priority_type_id not in('5','6')and datediff(hh,j.report_date, getdate()) >= pt.due_hours
)*1.
/
(
select count(*)[Count2]
from whd.job_ticket j
where j.status_type_id = '1' and j.deleted = '0'
) as numeric(10,2)) '% Overdue'

|||Nice Thanks!