Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

COUNT and NULL Expand / Collapse
Author
Message
Posted Tuesday, April 29, 2008 12:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 4, 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
Posted Tuesday, April 29, 2008 12:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:57 AM
Points: 1,093, Visits: 1,222

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
Posted Tuesday, April 29, 2008 12:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 4, 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
Posted Tuesday, April 29, 2008 1:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 4, 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


Wayne
Post #491859
Posted Tuesday, April 29, 2008 6:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:57 AM
Points: 1,093, Visits: 1,222
...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
Posted Tuesday, April 29, 2008 4:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 4, 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
Posted Tuesday, April 29, 2008 10:56 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:57 AM
Points: 1,093, Visits: 1,222


...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
Posted Tuesday, April 29, 2008 11:23 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
Hi

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


"Keep Trying"
Post #492638
Posted Wednesday, April 30, 2008 12:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:57 AM
Points: 1,093, Visits: 1,222
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
Posted Wednesday, April 30, 2008 2:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
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 »

Add to briefcase 12»»

Permissions Expand / Collapse