Help with the count function

Question

Post reply

Help with the count function

SQLTestUser

Hall of Fame

Points: 3997
More actions
June 2, 2014 at 3:57 pm

#307787

CREATE TABLE [dbo].[quality](
[qualityID] [int] NULL,
[qualitydesc] [nvarchar](100) NULL,
[IsActive] [bit] NULL
) ON [PRIMARY]
GO
data for the table qualityCheck is as follows
2010-11-30 00:00:00.0004XXXX
2010-12-15 00:00:00.0004YYYYY
2010-12-15 00:00:00.0004ZZZZZ
2013-07-11 00:00:00.0001Watever
2013-03-12 00:00:00.0002This
2012-12-03 00:00:00.0001that
2013-02-20 00:00:00.0001nothing
2011-10-14 00:00:00.0001To worry about
2013-03-28 00:00:00.0001this
2013-03-28 00:00:00.0001is
2011-11-15 00:00:00.0001a
2011-11-21 00:00:00.0001To worry about
2011-11-21 00:00:00.0001To worry about
2011-11-21 00:00:00.0001To worry about
2011-11-21 00:00:00.0001To worry about
2011-11-21 00:00:00.0001To worry about
2011-11-21 00:00:00.0001To worry about
2011-11-21 00:00:00.0001To worry about
2011-11-21 00:00:00.0001To worry about
2011-11-21 00:00:00.0001To worry about
2011-11-21 00:00:00.0001To worry about
2011-11-21 00:00:00.0001To worry about
2013-03-06 00:00:00.0003To worry about
2012-03-15 00:00:00.0001To worry about
2013-03-12 00:00:00.0001To worry about
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[quality](
[qualityID] [int] NULL,
[qualitydesc] [nvarchar](100) NULL,
[IsActive] [bit] NULL
) ON [PRIMARY]
GO
data for the table quality table is as follows
1IOTrue
2MinTrue
3MaxTrue
4RegTrue
5OtherTrue
then i run a query as follows for a count of the qualityDescriptions according to dueDate
SELECT COUNT(*) AS TotalCount,Quality.Qualitydesc,QualityCheck.DueDAte
FROM QualityCheck INNER JOIN Quality
ON QualityCheck.QualityID = Quality.qualityID
GROUP BY quality.qualityID,QualityCheck.DueDate,quality.qualitydesc
having
DATEDIFF(d,DueDate,GETDATE()) >= 90
and the result set of the query is as shown below
1IO2011-10-14 00:00:00.000
1IO2011-11-15 00:00:00.000
11IO2011-11-21 00:00:00.000
1IO2012-03-15 00:00:00.000
1IO2012-12-03 00:00:00.000
1IO2013-02-20 00:00:00.000
2IO2013-03-12 00:00:00.000
2IO2013-03-28 00:00:00.000
1IO2013-07-11 00:00:00.000
1Min2013-03-12 00:00:00.000
1Max2013-03-06 00:00:00.000
1Reg2010-11-30 00:00:00.000
2Reg2010-12-15 00:00:00.000
1Other2013-08-19 00:00:00.000
however i want the result to coalesce the count of IO into one row, Min's into one row i.e. the aggregate results to be displayed. something like below, is this possible ?
22IO90 Days elapsed
1Min90 Days elapsed
1Max90 Days elapsed
3Reg90 Days elapsed
1Other90 Days elapsed

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

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

Mark Fitzgerald-331224 SSCrazy Eights Points: 8489 More actions · Answer 1

Try this:

SELECT COUNT(*) AS TotalCount,Quality.Qualitydesc

FROM QualityCheck INNER JOIN Quality

ON QualityCheck.QualityID = Quality.qualityID

WHERE DATEDIFF(d,DueDate,GETDATE()) >= 90

GROUP BY quality.qualityDesc

The code section you posted as the table definitions were the same so just guessed the table definition of quality check.

Fitz

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 2

Thanks that was what i was exactly looking for

thanks again

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

You've received what you wanted, but here's some additional help. 😉

In the future, review the DDL and sample data that you post. You posted the same table twice and missed one table. You should post your sample data as insert statements to make easier to test the code before posting a solution. Something like this:

CREATE TABLE #qualitycheck(

[DueDate] [datetime] NULL,

[qualityID] [int] NULL,

[Sometext] [nvarchar](100) NULL

)

INSERT INTO #qualitycheck VALUES(

'2010-11-30 00:00:00.000',4,'XXXX'),(

'2010-12-15 00:00:00.000',4,'YYYYY'),(

'2010-12-15 00:00:00.000',4,'ZZZZZ'),(

'2013-07-11 00:00:00.000',1,'Watever'),(

'2013-03-12 00:00:00.000',2,'This'),(

'2012-12-03 00:00:00.000',1,'that '),(

'2013-02-20 00:00:00.000',1,'nothing'),(

'2011-10-14 00:00:00.000',1,'To worry about'),(

'2013-03-28 00:00:00.000',1,'this '),(

'2013-03-28 00:00:00.000',1,'is '),(

'2011-11-15 00:00:00.000',1,'a'),(

'2011-11-21 00:00:00.000',1,'To worry about'),(

'2013-03-06 00:00:00.000',3,'To worry about'),(

'2012-03-15 00:00:00.000',1,'To worry about'),(

'2013-03-12 00:00:00.000',1,'To worry about')

CREATE TABLE #quality(

[qualityID] [int] NULL,

[qualitydesc] [nvarchar](100) NULL,

[IsActive] [bit] NULL

)

INSERT INTO #quality VALUES(

1,'IO',1),(

2,'Min',1),(

3,'Max',1),(

4,'Reg',1),(

5,'Other',1)

Knowing that, let's review the code. There's a non-SARGable clause that you might want to change. If you have functions in your columns, you won't be able to use indexes correctly. The first solution that comes to mind is to change it like this:

WHERE DueDate <= DATEADD(d,-90,GETDATE())

But that date will include time, so we might want to remove time. A more complex function can help with that.

WHERE DueDate <= DATEADD(d,DATEDIFF( DD, 90, GETDATE()), 0)

If you want to check the differences, run the following code.

SELECT DATEADD(d,-90,GETDATE()) UNION ALL

SELECT DATEADD(d,DATEDIFF( DD, 90, GETDATE()), 0)

Try to understand how that works and it will help you with many date/time calculations. If you have questions, feel free to ask. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 4

I thought i was done but didnot account for values with a count of '0'

So how would i return '0' for descriptions i.e. any of the results that do not have a value. So in my example if the quality "reg" had no values in the quality check column instead of not showing the "Reg" is there a way to return a 0 value ? . As a result the data would look something like this

22IO90 Days elapsed

1Min90 Days elapsed

1Max90 Days elapsed

0Reg90 Days elapsed

1Other90 Days elapsed

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

I couldn't get the results with the sample data that you posted, but this should be what you're looking for.

SELECT COUNT(qc.QualityID) AS TotalCount,

q.Qualitydesc

FROM Quality q

LEFT

JOIN QualityCheck qc ON qc.QualityID = q.qualityID

AND qc.DueDate >= DATEADD(d,DATEDIFF( DD, 90, GETDATE()), 0)

GROUP BY q.qualityDesc

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 6

SQLTestUser

Hall of Fame

Points: 3997

June 5, 2014 at 2:43 pm

#1719461

Sweeeeeeeeeetttttt

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

SQLTestUser (6/5/2014)
Sweeeeeeeeeetttttt

The question is, do you understand the concept of "SARGability" the Luis explained? I ask because it's probably the single greatest obstacle to high performance code there is... well, except maybe for ORMs assigning the wrong datatype to constants. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)