COUNT and NULL

Question

Post reply

COUNT and NULL

wayne.cursor

Valued Member

Points: 61
More actions
April 29, 2008 at 12:02 am

#187072

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

Viewing 15 posts - 1 through 15 (of 15 total)

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

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]

Select category, count(*) From {Table}

Where createdDate Between {start date} And {end date}

Group By category

confirm on this.

Mahesh

[/font]

MH-09-AM-8694

wayne.cursor Valued Member Points: 61 More actions · Answer 2

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

wayne.cursor Valued Member Points: 61 More actions · Answer 3

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 🙂

Wayne

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 4

[font="Verdana"]

...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

[/font]

MH-09-AM-8694

wayne.cursor Valued Member Points: 61 More actions · Answer 5

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"

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 6

[font="Verdana"]

...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

[/font]

MH-09-AM-8694

ChiragNS One Orange Chip Points: 26137 More actions · Answer 7

Hi

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

"Keep Trying"

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 8

[font="Verdana"]

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

[/font]

MH-09-AM-8694

ChiragNS One Orange Chip Points: 26137 More actions · Answer 9

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"

janine.rawnsley Ten Centuries Points: 1056 More actions · Answer 10

If I'm understanding your requirement correctly, something like this? Sorry if I've over-complicated but I'm trying to cater for all possible scanrios here (including there being no jobs at all in your date range, but still needing to list the skill)

SELECTSG.dbJobSkillGrp,

JC.JobsCreated,

JF.JobsFilled,

JV.JobsVacant

-- Get all job skills (bearing in mind some won't have any created, filled OR vacant in the last x days

FROM(SELECT DISTINCT dbJobSkillGrp FROM ftjoborder) SG

-- Get # of jobs created in date range for each skillset

LEFT OUTER JOIN(SELECT dbJobSkillGrp, COUNT(*) as 'JobsCreated'

FROMftjoborder

WHEREdbJobCreateDate BETWEEN @StartDate AND @EndDate

GROUP BY dbJobSkillGrp) JC

ONJC.dbJobSkillGrp = SG.dbJobSkillGrp

-- Get all jobs filled in date range

LEFT OUTER JOIN(SELECT dbJobSkillGrp, COUNT(*) as 'JobsFilled'

FROMftjoborder

WHEREdbJobFillDate BETWEEN @StartDate AND @EndDate

GROUP BY dbJobSkillGrp) JF

ONJF.dbJobSkillGrp = SG.dbJobSkillGrp

-- Get all vacant jobs

LEFT OUTER JOIN(SELECT dbJobSkillGrp, COUNT(*) as 'JobsVacant'

FROMftjoborder

WHEREdbJobStatus = 'Vacant'

ANDdbJobCreateDate BETWEEN @StartDate AND @EndDate -- Do you need this??

GROUP BY dbJobSkillGrp) JV

ONJV.dbJobSkillGrp = SG.dbJobSkillGrp

ORDER BY

SG.dbJobSkillGrp

Dugi SSCoach Points: 17998 More actions · Answer 11

then...after janine.rawnsley's post what is the final word for this topic to use join or "or" operator!

or it depends from the relations between tables..plz advice!

???

:hehe:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

janine.rawnsley Ten Centuries Points: 1056 More actions · Answer 12

I've made a few assumptions hence my larger query:

1, You won't always have a job within a category with a create date or filled date that falls within the desired date range

2, You only want count of vacant jobs which were created within the date range (see comment inside 4th subquery)

Rather than talking about optimization at this point, I'm saying the previous suggestions just won't work if you have some categories you want to show but no data for the date range. I've even set up a table with same data as yours to test this theory.

wayne.cursor Valued Member Points: 61 More actions · Answer 13

WOW!

again... WOW!!!!

Janine, i am very very grateful, and very very very impressed... i don't think i could have figured that out.

I'll buy you a drink next time i see you!

so... just to cover this off, here is the query i ended up using

SELECT

SG.dbJobSkillGrp as Skill_Group, SG.dbJobSkillGrp AS recNumber, JC.JobsCreated AS Jobs_Created_Last_7_Days, JF.JobsFilled AS Jobs_Filled_Last_7_Days, JV.JobsVacant AS Jobs_Vacant

FROM ((((

SELECT DISTINCT dbJobSkillGrp FROM ftjoborder) SG

LEFT OUTER JOIN (SELECT dbJobSkillGrp, COUNT(*) as JobsCreated

FROM ftjoborder

WHERE dbJobCreateDate > #24/04/2008#

GROUP BY dbJobSkillGrp) JC

ON JC.dbJobSkillGrp = SG.dbJobSkillGrp )

LEFT OUTER JOIN (SELECT dbJobSkillGrp, COUNT(*) as JobsFilled

FROM ftjoborder

WHERE dbJobFillDate > #24/04/2008#

GROUP BY dbJobSkillGrp) JF

ON JF.dbJobSkillGrp = SG.dbJobSkillGrp )

LEFT OUTER JOIN (SELECT dbJobSkillGrp, COUNT(*) as JobsVacant

FROM ftjoborder

WHERE dbJobStatus = 'Vacant'

GROUP BY dbJobSkillGrp) JV

ON JV.dbJobSkillGrp = SG.dbJobSkillGrp )

ORDER BY SG.dbJobSkillGrp

and because this is being used in an ASP environment I USE

'dbJobFillDate > #' & Date - 7 & '# '

to give me the current date range

and here is the proof... the report it generates looks like this:

Janine you are a star! In fact in the version tracking log of the application i am using this report in i have added an thank you note for you.

many thanks.

Let me know if there is anything i can do for you.

Regards

Wayne

janine.rawnsley Ten Centuries Points: 1056 More actions · Answer 14

janine.rawnsley

Ten Centuries

Points: 1056

May 1, 2008 at 3:01 am

#809463

No problem at all - glad I could help 🙂