Combining results in query... - 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 2000 Forums

SQL Server Development (2000)

Combining results in query...

Author

Topic

NewMedia42
Starting Member

35 Posts

Posted - 2003-04-19 : 13:40:29

I'm in the process of optimizing some queries I perform on a pretty regular basis, and I've run into one that I'm not sure what would be the best way to go about improving.

I basically run the following queries:

SELECT TOP 10 Item.Created, AVG(Item.Value1) WHERE (Item.Status!=0) GROUP BY Item.Created ORDER BY Item.Created DESC
SELECT TOP 10 Item.Created, AVG(Item.Value2) WHERE (Item.Status==1) GROUP BY Item.Created ORDER BY Item.Created DESC
SELECT TOP 10 Item.Created, AVG(Item.Value3) WHERE (Item.Status!=0) GROUP BY Item.Created ORDER BY Item.Created DESC

So I could easily combine them into:

SELECT TOP 10 Item.Created, AVG(Item.Value1), AVG(Item.Value2), AVG(Item.Value3) WHERE (Item.Status!=0) GROUP BY Item.Created ORDER BY Item.Created DESC

If not for the second queries need to have the Item.Status==1 ... Is there a way I structure a query that will still perform like this? Thanks in advance!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-19 : 15:00:06

Take a look at these queries, see if it leads to the answer ....

a)

SELECT SUM(Amount) as Type1Sum, SUM(Amount) / COUNT(*) as Type1Avg
FROM
Data
WHERE Type = 1

b)

SELECT SUM(Amount) as Type2Sum, SUM(Amount) / COUNT(*) as Type2Avg
FROM
Data
WHERE Type = 2

c)

SELECT
SUM(CASE WHEN Type = 1 THEN Amount ELSE 0 END) as Type1Sum,
SUM(CASE WHEN Type = 1 THEN Amount ELSE 0 END) /
SUM(CASE WHEN Type = 1 THEN 1 ELSE 0 END) as Type1Avg,
SUM(CASE WHEN Type = 2 THEN Amount ELSE 0 END) as Type2Sum,
SUM(CASE WHEN Type = 2 THEN Amount ELSE 0 END) /
SUM(CASE WHEN Type = 2 THEN 1 ELSE 0 END) as Type2Avg,
FROM
Data
WHERE Type in (1,2)

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources