COUNT and NULL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » SQL Server Newbies » COUNT and NULL

16 posts, Page 1 of 2

1 2 »»

COUNT and NULL

Rate Topic

Display Mode

Topic Options

Author

Message

wayne.cursor

Posted Tuesday, April 29, 2008 12:02 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 04, 2008 8:14 AM
Points: 5, Visits: 18

Hi SQL Gurus,

I have a question about using the COUNT function. How can I write a query that will return the count value of records created within a certain time frame... say the last 7 days, grouped by a CATEGORY column?

so my database table would have the fields...

name, category, createdDate

And i want my query to generate a recordset that looks like

Category, Count
--------,------
category1, 10
category2, 20
category3, 15

which is displaying the Count of number of records created in the last 7 days, for each Category.

Any ideas would be greatly appreciated.

Thanks

Wayne

Post #491843

Mahesh Bote

Posted Tuesday, April 29, 2008 12:17 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, February 21, 2013 6:22 AM
Points: 1,074, Visits: 1,205

Select category, count(*) From {Table}
Where createdDate Between {start date} And {end date}
Group By category

confirm on this.

Mahesh

MH-09-AM-8694

Post #491848

wayne.cursor

Posted Tuesday, April 29, 2008 12:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 04, 2008 8:14 AM
Points: 5, Visits: 18

Hi Mahesh

thanks for your quick reply.

What you have posted is exactly what i was after. Except, it doesn't return all categories. i think it ignores Categories which have a 0 (zero) count value.

any ideas on how to get around this?

Thanks

Wayne

Post #491851

wayne.cursor

Posted Tuesday, April 29, 2008 1:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 04, 2008 8:14 AM
Points: 5, Visits: 18

Actually, thinking a bit further down the track... what i am actually after is...

I have a table like this:

name, category, createdDate, status, statusDate

and i want to create a recordset like this:

category, created_Count, status_Count
--------, -------------, -------------
category1, 10, 6
category2, 0, 6
category3, 10, 0
category4, 0, 0

which is displaying the Count of number of records created in the last 7 days for each Category, and the Count of number of records at a particular status for each category which have achieved that status in the last 30 days. Including showing categories that have a 0(zero) count.

am i asking the impossible?

Again, thanks for all assistance Smile

Wayne

Post #491859

Mahesh Bote

Posted Tuesday, April 29, 2008 6:18 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, February 21, 2013 6:22 AM
Points: 1,074, Visits: 1,205

...am i asking the impossible?

Certainly not. I have suggested the solution as per the 1st post. You havn't mentioned these possiblities, otherwise I would have been considered it as well.

anyways, please give some more details on this with real life data.

Mahesh

MH-09-AM-8694

Post #492022

wayne.cursor

Posted Tuesday, April 29, 2008 4:58 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 04, 2008 8:14 AM
Points: 5, Visits: 18

Hi Mahesh,

here is the report that im trying to generate:

notice how there are only Skill_Groups listed that have COUNT values for them. I also need to show Skill_Groups that do not have any Jobs_Created_Last_7_Days, etc.

Here is the source table with data that i am querying.

the table is called "ftjoborder"

Post #492533

Mahesh Bote

Posted Tuesday, April 29, 2008 10:56 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, February 21, 2013 6:22 AM
Points: 1,074, Visits: 1,205

...I also need to show Skill_Groups that do not have any Jobs_Created_Last_7_Days...

Select      category, count(dbjobskillgrp), count(dbjobstatus)
From        {Table}
Where      (dbjobcreatedDate Between {start date} And {end date} Or            dbjobcreatedDate Is Null)
Group By   category

confirm on this.

Mahesh

MH-09-AM-8694

Post #492627

ChiragNS

Posted Tuesday, April 29, 2008 11:23 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

Hi

What about using a Left join instead of "OR". This will have a better execution plan.

"Keep Trying"

Post #492638

Mahesh Bote

Posted Wednesday, April 30, 2008 12:45 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, February 21, 2013 6:22 AM
Points: 1,074, Visits: 1,205

Chirag (4/29/2008)

Hi

What about using a Left join instead of "OR". This will have a better execution plan.

Do you mean, joining TableA to TableA with left outer join will give better performance than using or?

Mahesh

MH-09-AM-8694

Post #492672

ChiragNS

Posted Wednesday, April 30, 2008 2:43 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

Experts correct me if iam wromg

when OR is used sql server finds it difficult to use the right index , mostly it does a table scan. This can bring down the performance .

"Keep Trying"

Post #492728

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions