January 7, 2005 at 9:50 pm
Hello,
I can't get the data from the specified table. for example
Select Item, ISNULL(Sum(Qty),0) 'Qty'
From Table1
Where Item=100
According to the above query, if the item is not in Table1, no result will be come out. I want to get the following result.
Result : (Item) 100 (Qty) 0
Please help me !!!
January 8, 2005 at 3:59 am
So even though the item field may not exist at all in table1, you still want a row to appear in your resultset?
Does item 100 appear in some other table? Thinking about doing a left join from a 'master' table of items to table1:
select t.item, sum(t.qty) as TotalQty
from table1 t left outer join
master m on m.item = t.item
where t.item = 100
group by t.item
As long as there is a (single) row in the master table for item 100, this query should give you what you need.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 10, 2005 at 2:52 am
Just a note on the original query. The SUM aggregate is pretty useless without a GROUP BY. In fact, your query should yield an error
use northwind
select orderid, isnull(sum(unitprice),0) unitprice
from [order details]
where orderid=10248
--group by orderid
Server: Nachr.-Nr. 8118, Schweregrad 16, Status 1, Zeile 2
Die order details.OrderID-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion enthalten und keine GROUP BY-Klausel vorhanden ist.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 10, 2005 at 7:24 am
Also use SUM(ISNULL(Qty,0))
to avoid the
Warning: Null value is eliminated by an aggregate or other SET operation.
message
Far away is close at hand in the images of elsewhere.
Anon.
January 10, 2005 at 8:43 pm
Dear all,
Thank you so much for your suggestion. I get it what I need using master table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply