Home » SQL & PL/SQL » SQL & PL/SQL » SQL select doubt
SQL select doubt [message #250729] Wed, 11 July 2007 01:56 Go to next message
frank.svs
Messages: 162
Registered: February 2006
Senior Member


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


Re: SQL select doubt [message #250740 is a reply to message #250729] Wed, 11 July 2007 02:13 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
I think the database is sqlserver. U need to union all ur select statements which are used in update and then need to update the whole table at once.
Re: SQL select doubt [message #250746 is a reply to message #250729] Wed, 11 July 2007 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Is this an Oracle question?
Note this is an Oracle forum.

Regards
Michel

Re: SQL select doubt [message #250826 is a reply to message #250729] Wed, 11 July 2007 08:15 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Unbelieveable that after all these years you still so not know how to format your posts or cut and paste actual SQL commands. You post code that does not work; there are no semi-colons.
Previous Topic: Send Mail thru Exchange server
Next Topic: Calculating age
Goto Forum:
  


Current Time: Wed Dec 07 14:10:38 CST 2016

Total time taken to generate the page: 0.13513 seconds