Union Question

Question

Union Question

vani_r14

SSCrazy

Points: 2843
More actions
November 10, 2010 at 2:07 am

#100326

Hi all
I am working on a report which pulls out costs for different items. I have the following format
(Select t.a, t.b, u.item, isnull(sum(u.cost), 0) as Cost
from (select a,b from Table where condition = 'Y') t,
table where condition = 'N') b
group by cols ....
When i run the above query it works fine when there is data but if a row returns no data its not returning a 0 instead it returns no data...
My query - can we have an isnull when there is a from clause query - please help
Thanks
Vani

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

It looks like you need to re-write your query to use a LEFT OUTER JOIN between the tables.

If you need more help, please read the first article in my signature, then post appropriate CREATE TABLE/INSERT statements to give us some sample data to work with.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

GSquared SSC Guru Points: 260824 More actions · Answer 2

If you need to return zeroes for combinations that don't show up in the measure, you need to write it as an inline sub-query.

They look like this:

select a, b,

(select count(*)

from TableWithMeasure

where ColA = OuterTable.ColA and ColB = OuterTable.ColB) as Qty

from OuterTable;

The "outer table" should be one where you have all the combinations of A and B that you want to count, including the ones that should show 0. The "measure table" is the one that keeps track of the individual actions that you're counting.

If, for example, you want to know the number of orders per customer in a time period, A and B would come from a Customers table and a Calendar table with the time periods in it, and the sub-query would select from an Orders table.

Does that make sense?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

WayneS SSC Guru Points: 95460 More actions · Answer 3

Good catch GSquared.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

vani_r14 SSCrazy Points: 2843 More actions · Answer 4

Hi all

Thanks for your help. I got it now and its working fine...

Thanks again

Cheers

GSquared SSC Guru Points: 260824 More actions · Answer 5

You're welcome.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon