May 4, 2012 at 7:41 am
Can someone help me figure out the logic on how to deliver the count after I aggregated the rows?
For example:
Select ProductID, Location, sum(CASES) as TotalCases
From TblProduct
Group by ProductID, Locations
Having (sum(cases) <> 0)
However, I am more interested in count of the rows that I return.
Now, I am a bit lost on how I simply count the number of rows there are. First I thought about Select count (*) from TblProduct then joining to the aggregate table?
I know this is a simple question, but I can't wrap my head around head it!
Thank you.
May 4, 2012 at 7:50 am
if this is the only step in the batch you could just select @@rowcount, if not you will need to declare a varable and set that variable = @@rowcount at the end of that statement so that its stored throughout the entire batch
other options could be to wrap the statement into a CTE and use a ranking function like ROW_NUMBER() then select the max row number from the cte, insert the values into a temp table and then do select count(*) from that table.
May 4, 2012 at 7:56 am
im having a little dificulty understanding your requirements. do you have multiple entries for a location and product id that you need to sum the cases for??
Can you post DDL and Sample data and how you would like your results. Please see the first link in my signature for help on the best way to post the create table scripts and sample data and results.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 4, 2012 at 8:01 am
How about:
SELECT COUNT(*)
FROM
(
SELECT ProductID, Location, SUM(cases) as TotalCases
FROM dbo.TblProduct
GROUP BY ProductID, Locations
HAVING (SUM(cases) <> 0)
) AS Query;
May 4, 2012 at 8:07 am
Hmm...seems like I was overthinking tremendously! Thank you for your help. I apologize for not being clearer in my explanation earlier, I should of posted the supplementary information. I guess I was lucky for asking such a simple question..haha
May 4, 2012 at 8:26 am
bostonjoey (5/4/2012)
Hmm...seems like I was overthinking tremendously! Thank you for your help. I apologize for not being clearer in my explanation earlier, I should of posted the supplementary information. I guess I was lucky for asking such a simple question..haha
na i was just a little confused my self. if im confused ill ask for the ddl and sample data since it normally clears every thing up and along with the info comes a better explanation of the problem.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply