count the rows

Question

Post reply

count the rows

maha_dhina

SSC Rookie

Points: 28
More actions
July 25, 2008 at 2:01 pm

#190213

i am a new sql developer.i need to count the rows of a table for today,30 days back,90 days back and 180 days back.can anyone please help me how to write a query for this.thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Do you have a date column in the table?

What is the structure of the table?

What have you tried so far?

You want the Count() function in the select and in the Where you will want to use the DateAdd function. You can look that up in Books On Line (BOL).

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

maha_dhina SSC Rookie Points: 28 More actions · Answer 2

i have data in my table.i need to count the open issues for today ,30 days back,90 days back,180 days back.

i wrote a stored procedure

select projectname,count('x') as "today's count",(select count('x') from table1 where(datediff(day,date_submitted.getdate())>=30) group by projectname)as "30 days back count",(select.......).

when i write stored procedure like this i get a error as subquery returns more than 1 row.

so i tried to write the subquery in the from clause.like

select projectname,count('x) as "todays count"

from(select count('x') where datediff(day,date_submitted,getdate())>=30) as "30 days bck count

from table1

groupby projectname)

here too i get the error as incorrect keyword near from.i don't know how to fix it.can you please help me.thanks

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Do you need the data passed out as columns or rows?

I would do something like this:

[font="Courier New"]SELECT

projectname,

COUNT(*) AS open_issues_for_180,

SUM(CASE

WHEN datesubmitted >= DATEADD(DAY, -90, GETDATE()) THEN 1

ELSE 0

END) AS open_issues_for_90,

SUM(CASE

WHEN datesubmitted >= DATEADD(DAY, -30, GETDATE()) THEN 1

ELSE 0

END) AS open_issues_for_30

FROM

dbo.TABLE

WHERE

DateSubmitted < GETDATE() AND

DateSubmitted >= DATEADD(DAY, -30, GETDATE())

GROUP BY

projectname[/font]

This does everything in one pass and eliminates using a function on a column in the where clause which would reduce index usage.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Venkatesan Prabu SSCommitted Points: 1892 More actions · Answer 4

Below query,

select count(*) from table

will fetch you the count of rows in your table.

If you have date column then you can modify the query like,

select count(*) from table where datecolumn = getdate()

will fetch you the count of rows for today.

for 30 days before data

select count(*) from table where datecolumn = dateadd(getdate(),-30)

will give you the data before 30 days.

Happy learning!!!

Regards,

Venkatesan Prabu. J

Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:

http://venkattechnicalblog.blogspot.com/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Venkatesan Prabu (7/26/2008)
Below query,
select count(*) from table
will fetch you the count of rows in your table.
If you have date column then you can modify the query like,
select count(*) from table where datecolumn = getdate()
will fetch you the count of rows for today.
for 30 days before data
select count(*) from table where datecolumn = dateadd(getdate(),-30)
will give you the data before 30 days.
Happy learning!!!
Regards,
Venkatesan Prabu. J

Venkatesan, if I may say so, this is a most unhelpful post.

Firstly,

select count(*) from table where datecolumn = getdate() will not retrieve the count of rows for today, it will only return rows where the datecolumn exactly matches the value returned by getdate(), which is a datetime. A match is unlikely, and the result of

select count(*) from table where datecolumn = getdate() is almost certainly going to be 0.

Secondly, dateadd(getdate(),-30) returns an error. The correct syntax, as used by Jack above, is dateadd(dd,-30,getdate())

Thirdly, select count(*) from table where datecolumn = dateadd(getdate(),-30) will not work - it will only return rows where datecolumn exactly matches the datetime returned by dateadd(getdate(),-30) Note, the OP wants the rowcount returned for a date range, not a point in time.

If you take some time examining Jack's post, you will see that he has met and even exceeded the expectations of the OP. He's written a single complete working query which does the whole job in one pass without errors. He's written it in a clear and concise style which requires little or no documentation and has offered the OP some hints for performance in the future. It's an excellent example and one which we all aspire to.

Here's a couple of suggestions which help you to provide good quality solutions like Jack's;

Always test your code. Create some test data if necessary - it almost always is. If your code doesn't work or doesn't meet the requirements of the OP, don't post, wait for someone else to post a solution and learn from it.

Best regards

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

maha_dhina SSC Rookie Points: 28 More actions · Answer 6

thank you soooooooooooooooooooooooooooo much for your help.

maha_dhina SSC Rookie Points: 28 More actions · Answer 7

thank you sooooooooooooooooooooooooooooooooooooooooooooooooooooo much for your help jack.

your code worked.