Query help - replace 0 for no values.

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

Transact-SQL (2000)

Query help - replace 0 for no values.

Author

Topic

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-05-06 : 09:47:00

I need help for the following.

I have a table
TableA
Col1, Col2, Category, Date, Type

I am doing

Select Avg(Col1) Avrg, Sum(Col2) TotNum, Category
FROM A
Where Date= somedate
AND Type=sometype.
Group by Category

The result is
Avrg TotNum Category

6.7 500 C1

With these conditions I am only getting one category's data.
I want to make the other categories as 0 if there is no data but I want the rest of the categories to show up too.

The result should be

Avrg TotNum Category

6.7 500 C1
0 0 C2
0 0 C3
0 0 C4
0 0 C5

I tried doing union as follows but the problem is it will have dupls.

Select Category, Avg(Col1) Avrg, Sum(Col2) TotNum
FROM TableA
Where Date=somedate
AND Type=sometype
GROUP by Category

union all

Select distinct Category,0 as Avrg, 0 as TotNum
FROM TableA

Is there any better way than for me to do sub queries and unions?
Looks like a simplest thing to do, but I can't think of a SIMPLER solution.

Any advices please ?

TIA

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2004-05-06 : 11:12:37

[code]
Select Avg(case when Date = Somedate and type = sometype then Col1
else 0 end) Avrg,
Sum(case when date = somedate and type = sometype then Col2
else 0 end) TotNum, Category
FROM A
Group by Category
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources