COUNT

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Newbies » COUNT

COUNT

Rate Topic

Display Mode

Topic Options

Author

Message

kabaari

Posted Monday, August 17, 2009 7:18 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 5:54 AM
Points: 87, Visits: 287

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

Post #771882

kevriley

Posted Monday, August 17, 2009 8:45 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:01 PM
Points: 2,677, Visits: 2,273

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

Post #771968

kabaari

Posted Monday, August 17, 2009 9:03 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 5:54 AM
Points: 87, Visits: 287

I just tried your script and this is my result:

"Error in list of function arguments: '.' not recognized.
Unable to parse query text."

Post #771986

kevriley

Posted Monday, August 17, 2009 9:23 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:01 PM
Points: 2,677, Visits: 2,273

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

Post #772002

kabaari

Posted Monday, August 17, 2009 9:37 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 5:54 AM
Points: 87, Visits: 287

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

Post #772016

kevriley

Posted Monday, August 17, 2009 9:43 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:01 PM
Points: 2,677, Visits: 2,273

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

Post #772021

kabaari

Posted Monday, August 17, 2009 9:56 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 5:54 AM
Points: 87, Visits: 287

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

Post #772041

WayneS

Posted Monday, August 17, 2009 10:07 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:38 PM
Points: 6,368, Visits: 8,230

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

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

Post #772056

kabaari

Posted Monday, August 17, 2009 10:55 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 5:54 AM
Points: 87, Visits: 287

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.

Post #772104

« Prev Topic | Next Topic »

Permissions