Multiple query results in one row

  • I need help please...

     

    Say I have three Group Types like:

    A  

    B

    C

    I have to write one select statement that will be give me number of A's, B's , C's and total in table .. the result should be

    A  B  C Total

    3 5 6     14

     

    Can anyone help me in this please. Thanks in advance.

  • something from the books online (pivot table)

     

    SELECT togroupby,

        SUM(CASE Group WHEN 1 THEN Amount ELSE 0 END) AS A,

        SUM(CASE Group WHEN 2 THEN Amount ELSE 0 END) AS B,

        SUM(CASE Group WHEN 3 THEN Amount ELSE 0 END) AS C,

        count(*) as TOTAL

    FROM ...

    GROUP BY togroupby

  • If you do not know all your Groups and want to do this dynamically, this is clumsy, but it will work...

    CREATE TABLE #Counting( [Name] varchar(1),

                                              [Count] integer)

    INSERT INTO #Counting

    SELECT 'A', 2     UNION ALL

    SELECT 'A', 1     UNION ALL

    SELECT 'B', 2     UNION ALL

    SELECT 'B', 1     UNION ALL

    SELECT 'B', 1     UNION ALL

    SELECT 'B', 1     UNION ALL

    SELECT 'C', 3     UNION ALL

    SELECT 'C', 3

    CREATE TABLE #OutPut( [Total] integer)

    INSERT INTO #OutPut( [Total])

    SELECT SUM( [Count]) FROM #Counting

    DECLARE @CurrentName varchar(1),

                    @MaxName varchar(1),

                    @SQL varchar(2000)

    SET @CurrentName = (SELECT MIN( [Name]) FROM #Counting)

    SET @MaxName = (SELECT MAX( [Name]) FROM #Counting)

    WHILE @CurrentName <= @MaxName

    BEGIN

         SET @SQL = 'ALTER TABLE #OutPut ADD [' + @CurrentName + '] integer NULL '

         EXEC( @SQL)

         SET @SQL = ''

         SET @SQL = 'UPDATE #OutPut SET [' + @CurrentName + '] = ' +

                              '(SELECT SUM( [Count]) FROM #Counting WHERE [Name] = ' +

                              CHAR(39) + @CurrentName  + CHAR(39) + ')'

         EXEC( @SQL)

         SET @CurrentName = (SELECT MIN( [Name]) FROM #Counting WHERE [Name] > @CurrentName)

    END

    SELECT * FROM #OutPut

    DROP TABLE #OutPut

    DROP TABLE #Counting

    I wasn't born stupid - I had to study.

  • Here's another way to do it if don't know the groups (using Farrell's #Counting table).  This code below gives the following result, and is safe to run.

    A           B           C           TOTAL      

    ----------- ----------- ----------- -----------

    3           5           6           14

    CREATE TABLE #Counting( [Name] varchar(1), [Count] integer)

    INSERT INTO #Counting

    SELECT 'A', 2     UNION ALL

    SELECT 'A', 1     UNION ALL

    SELECT 'B', 2     UNION ALL

    SELECT 'B', 1     UNION ALL

    SELECT 'B', 1     UNION ALL

    SELECT 'B', 1     UNION ALL

    SELECT 'C', 3     UNION ALL

    SELECT 'C', 3

    DECLARE @s-2 VARCHAR(8000)

    SET @s-2 = ''

    SELECT @s-2 = @s-2 + 'SUM(CASE Name WHEN ''' + Name + ''' THEN Count ELSE 0 END) AS ' + Name + ',' + char(13)

    FROM (SELECT DISTINCT Name FROM #Counting) a

    EXEC ('SELECT ' + @s-2 + ' SUM(Count) as TOTAL FROM #Counting')

    DROP TABLE #Counting

    Regards,

    Ryan

     

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • Very nice!  Thanks Ryan - I will save this...

     

    I wasn't born stupid - I had to study.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply