Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: 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 NULL

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    some 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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Apologies:

    Code:
    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.
    Attached Files Attached Files

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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
    Last edited by Enigma; 09-22-06 at 00:30.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Do you want something like this.....BTW I am not sure...
    Code:
    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
    Last edited by rudra; 09-19-06 at 11:52.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    This will scan the table only once
    Code:
    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

  7. #7
    Join Date
    Jan 2002
    Posts
    189
    Quote Originally Posted by pdreyer
    This will scan the table only once
    Code:
    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

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Was that not equivalent to what I had posted ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Enigma
    Was that not equivalent to what I had posted ???
    nope, quite different
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •