question

tombiernacki avatar image
tombiernacki asked

how to get rowcount

Hi I am having an issue with getting total rowcount from my table based on the dates. What i need is total rowcount up the the date selected. for example my query is..... SELECT distinct [CensusDateKey] FROM my table where CensusDateKey >= 20130408 ---- my results are CensusDateKey 20130408 20130409 20130410 20130411 What I need is total rowcount as if those dates. I tried... SELECT distinct [CensusDateKey] ,COUNT(*)as 'RowCount' FROM mytable where CensusDateKey <= 20130408 group by CensusDateKey order by CensusDateKey asc But that gives me rowcount for those dates instead of running total count from day 1.
rowrow-countsrowcount
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

EuniceRH avatar image
EuniceRH answered
This is my first time answering a question, but this one looks fairly simple. SELECT COUNT(DISTINCT CensusDateKey) FROM mytable WHERE CensusDateKey <= 20130408
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the reply...That is not the solution though ... SELECT COUNT(DISTINCT CensusDateKey) FROM mytable WHERE CensusDateKey <= 20130408 gives the count of censusdatekey instead of total count of rows up to the date of censusdatekey
0 Likes 0 ·
You can google for "tsql running total" and find what you need. Here is my example....maybe it's not quite what you need, but will get you started... select o.CensusDateKey, count (o.PrimaryKeyID) as 'daily count', (select count(i.PrimaryKeyID) from mytalbe as i where i.CensusDateKey <= o.CensusDateKey AND i.CensusDateKey > 20130408) as 'running count' from mytalbe as o where o.CensusDateKey > 20130408 group by o.CensusDateKey the table labeled i is for the inner table the table labeled o is for the outer table is this homework? If so then I should have let you search.
0 Likes 0 ·
Thanks for the reply, I used this solution as well. This was not for homework I was just seeing if there is any other solutions. Thanks again.
0 Likes 0 ·
touhidmustakhim avatar image
touhidmustakhim answered
SELECT COUNT(distinct [CensusDateKey])as 'RowCount' FROM mytable where CensusDateKey <= 20130408 group by CensusDateKey order by CensusDateKey asc I think this should work for you.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.