SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


COUNT


COUNT

Author
Message
kabaari
kabaari
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 367
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
kevriley
kevriley
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3434 Visits: 2622
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
kabaari
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 367
I just tried your script and this is my result:

"Error in list of function arguments: '.' not recognized.
Unable to parse query text."
kevriley
kevriley
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3434 Visits: 2622
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
kabaari
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 367
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
01 Broken Lid 8/13/2009
99 Dropped on Floor 8/12/2009

Broken Lid = 3
Dropped on Floor = 1
kevriley
kevriley
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3434 Visits: 2622
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
kabaari
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 367
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
WayneS
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12189 Visits: 10601
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

kabaari
kabaari
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 367
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search