October 26, 2005 at 11:53 am
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.
October 26, 2005 at 12:18 pm
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
October 26, 2005 at 12:30 pm
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.
October 28, 2005 at 5:56 am
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.
October 28, 2005 at 6:54 am
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