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 :)