COUNT

Question

Post reply

COUNT

kabaari

SSCrazy

Points: 2235
More actions
August 17, 2009 at 7:18 am

#209603

Below is the SQL that works but not 100%. I need to count Titles so that I may chart my results. The Defect Titles are in a lookup table and the IDs are stored by INSERT in tbl_Assembly_Hold_Defects. Any help is greatly appreciated.
SELECT tlkp_Defects.Defect_Title, COUNT(tbl_Assembly_Hold_Defects.Defect_ID) AS Defect_Count, tbl_Assembly_Holds.Record_Date FROM tbl_Assembly_Hold_Defects, tlkp_Defects, tbl_Assembly_Holds
WHERE tbl_Assembly_Hold_Defects.Defect_ID = tlkp_Defects.Defect_ID AND tbl_Assembly_Holds.Record_Date BETWEEN '8/12/2009' AND '8/14/2009' AND tbl_Assembly_Holds.TagNumber = tbl_Assembly_Hold_Defects.TagNumber
GROUP BY tlkp_Defects.Defect_Title, tbl_Assembly_Holds.Record_Date, tbl_Assembly_Hold_Defects.Defect_ID, tlkp_Defects.Defect_ID, tbl_Assembly_Holds.TagNumber, tbl_Assembly_Hold_Defects.TagNumber

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

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

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 1

I think I understand your requirements, try this..

SELECT

count(tlkp_Defects.Defect_Title)

tbl_Assembly_Holds.Record_Date

FROM

tbl_Assembly_Hold_Defects

join tlkp_Defects on tbl_Assembly_Hold_Defects.Defect_ID = tlkp_Defects.Defect_ID

join tbl_Assembly_Holds on tbl_Assembly_Holds.TagNumber = tbl_Assembly_Hold_Defects.TagNumber

WHERE

tbl_Assembly_Holds.Record_Date BETWEEN '8/12/2009' AND '8/14/2009'

GROUP BY

tbl_Assembly_Holds.Record_Date

I have COUNTed the titles by date.

Also changed your join syntax to the more acceptable SQL92 standard.

Kev

kabaari SSCrazy Points: 2235 More actions · Answer 2

I just tried your script and this is my result:

"Error in list of function arguments: '.' not recognized.

Unable to parse query text."

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 3

I've just noticed I missed a comma out so try this

SELECT

count(Defect_Title),

tbl_Assembly_Holds.Record_Date

FROM

tbl_Assembly_Hold_Defects

join tlkp_Defects on tbl_Assembly_Hold_Defects.Defect_ID = tlkp_Defects.Defect_ID

join tbl_Assembly_Holds on tbl_Assembly_Holds.TagNumber = tbl_Assembly_Hold_Defects.TagNumber

WHERE

tbl_Assembly_Holds.Record_Date BETWEEN '8/12/2009' AND '8/14/2009'

GROUP BY

tbl_Assembly_Holds.Record_Date

kabaari SSCrazy Points: 2235 More actions · Answer 4

SSCrazy,

That worked but what I am looking for is:

1. Count the defects by title through a date range.

2. Chart the range

3. Show how many occurred throughtout the range and not per day.

For example:

Defect Record_Date

01 Broken Lid 8/12/2009

01 Broken Lid 8/13/2009

99 Dropped on Floor 8/12/2009

Broken Lid = 3

Dropped on Floor = 1

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 5

OK then

SELECT

tlkp_Defects.Defect_Title,

count(*)

FROM

tbl_Assembly_Hold_Defects

join tlkp_Defects on tbl_Assembly_Hold_Defects.Defect_ID = tlkp_Defects.Defect_ID

join tbl_Assembly_Holds on tbl_Assembly_Holds.TagNumber = tbl_Assembly_Hold_Defects.TagNumber

WHERE

tbl_Assembly_Holds.Record_Date BETWEEN '8/12/2009' AND '8/14/2009'

GROUP BY

tlkp_Defects.Defect_Title

kabaari SSCrazy Points: 2235 More actions · Answer 6

Thanks X 1000.

I made a couple of changes to your solution and I got the results I am looking for.

SELECT

tlkp_Defects.Defect_Title,

count(*)

FROM

tbl_Assembly_Hold_Defects

join tlkp_Defects on tbl_Assembly_Hold_Defects.Defect_ID = tlkp_Defects.Defect_ID

join tbl_Assembly_Holds on tbl_Assembly_Holds.TagNumber = tbl_Assembly_Hold_Defects.TagNumber

WHERE

tbl_Assembly_Holds.Record_Date BETWEEN '8/12/2009' AND '8/14/2009'

GROUP BY

tlkp_Defects.Defect_Title

WayneS SSC Guru Points: 95460 More actions · Answer 7

Just want to point out that you are using the BETWEEN operator with a date field... if your date field can have time values in it, then you probably are not getting what you want.

Each of the dates in the between clause will default to a time of "00:00:00.000". If you're looking for all dates on 8/14, then you need to either:

1. Record_date between '8/12/2009' and '8/14/2009 23:59:59.997'

OR

2. Record_date >= '8/12/2009' and Record_date < '8/15/2009'

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

kabaari SSCrazy Points: 2235 More actions · Answer 8

You make a good point. The results I was looking for was 8/12 thru 8/13 that's why I set it to those parameters. However, users may want to include "today" in their results.