NEED SELECT stmt - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

NEED SELECT stmt

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2007-07-11 : 02:55:22

Hi pals,

I need a SELECT which populates the aggregated data into a temp.Please help me out.

CREATE TABLE [dbo].[test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[col1] [int] NULL ,
[col2] [int] NULL ,
[place] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

id col1 col2 place status
1 3 2 kerala Progess
2 2 4 kerala Progess
3 2 4 kerala Progess
4 1 4 delhi Complete
5 2 5 delhi Complete
6 3 2 delhi Complete
7 2 4 hyd Incomplete
8 1 4 hyd Incomplete
9 1 4 hyd Incomplete

CREATE TABLE [dbo].[trg_tbl] (
[sid] [int] NULL ,
[sum_col1_Progress] [int] NULL ,
[avg_col2_Progress] [int] NULL ,
[sum_col1_Complete] [int] NULL ,
[avg_col2_Complete] [int] NULL ,
[sum_col1_Incomplete] [int] NULL ,
[avg_col2_Incomplete] [int] NULL
) ON [PRIMARY]
GO

Initially i inserted a dummy rec with all nulls in trg_tbl

My requirement is, i need to load the summarized data into the trg_bl Group by "place" field

UPDATE trg_tbl SET sum_col1_Progress =
(SELECT SUM(col1) FROM test
WHERE status=’Progress’
GROUP BY place )

UPDATE trg_tbl SET sum_col2_Progress =
(SELECT AVG(col2) FROM test
WHERE status=’Progress’
GROUP BY place )

UPDATE trg_tbl SET sum_col1_Complete =
(SELECT SUM(col1) FROM test
WHERE status=’Complete’
GROUP BY place )

UPDATE trg_tbl SET avg_col2_Complete =
(SELECT AVG(col2) FROM test
WHERE status=’Complete’
GROUP BY place )

UPDATE trg_tbl SET [sum_col2_Incomplete] =
(SELECT SUM(col1) FROM test
WHERE status=’Incomplete’
GROUP BY place )

UPDATE trg_tbl SET [sum_col2_Incomplete] =
(SELECT AVG(col2) FROM test
WHERE status=’Incomplete’
GROUP BY place )

Instead of writing so many updates can we write in a single SELECT stmt which select's all the aggregated data and loads into tmp table with above aggregated column names.

Any suggestions will be appreciated.

Thanks and regards,
franky

Kristen
Test

22859 Posts

Posted - 2007-07-11 : 03:04:15

I usually use something along these lines:


SELECT place,
       [ProgressSum] = SUM(CASE WHEN status=’Progress’ THEN col1 ELSE 0 END),
       [ProgressCount] = SUM(CASE WHEN status=’Progress’ THEN 1 ELSE 0 END),
       [CompleteSum] = SUM(CASE WHEN status=’Complete’ THEN col1 ELSE 0 END),
       [CompleteCount] = SUM(CASE WHEN status=’Complete’ THEN 1 ELSE 0 END),
...
FROM test 
GROUP BY place

for the Average you'll have to divide ProgressSum / ProgressCount

Kristen