July 3, 2014 at 12:21 pm
I am trying to get a "Zero Values" report. The following code works except when there is no child row.
declare @m table
(
parcel char(1)
)
insert @m values ('A'), ('B'), ('C'), ('D'), ('E')
declare @v-2 table
(
parcel char(1)
,value int
)
insert @v-2 (parcel, value) values
('A', 1) ,('A', 2)
,('B', 0)
,('C', 1) ,('C', 3) ,('C', 4)
,('E', 0)
SELECT M.parcel, v.sumValue
FROM @m M
JOIN
(select Parcel, SUM(value) sumValue
from @v-2
group by Parcel
Having SUM(Value) = 0
) v on v.Parcel = m.Parcel
I need the output to be:
B 0
D 0
E 0
Thanks!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 3, 2014 at 12:32 pm
Something like this.
select m.parcel, isnull(SUM(v.Value), 0)
from @m m
left join @v-2 v on m.parcel = v.parcel
group by m.parcel
having ISNULL(sum(v.Value), 0) = 0
order by m.parcel
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 4, 2014 at 4:01 am
One more way to do the same thing..
SELECTM.parcel, 0 AS [Count]
FROM@m AS M
WHERENOT EXISTS
(
SELECT*
FROM@v-2 AS V
WHEREM.parcel = V.parcel AND V.value > 0
)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 4, 2014 at 5:44 pm
Thank you Sean & Kingston! Both solutions worked well. But Kingston's was just a touch faster.
Returning 88 rows from 46K, Sean's fastest time was .374 and Kingston's was .336.
I can live with either of those times!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 6, 2014 at 12:24 am
Out of curiosity wondering how does this perform ?
SELECT v.parcel,v.value
FROM @m M
JOIN @v-2 v
on v.Parcel = m.Parcel
group by v.parcel,v.value
having sum(case when v.value=0 then 0 else 1 end)=0
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
July 7, 2014 at 9:31 am
Sachin Nandanwar (7/6/2014)
Out of curiosity wondering how does this perform ?
I tried your solution with the posted test data and it does not return the required result set. So, I didn't go much further than that.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply