SQL Aggregate Function Query not Producing Expected Results

Question

The following is my query to go through about a million rows to calculate MTBUR (Mean Time Before Unscheduled Repair):

DECLARE @BeginDate date = '01-01-2013', 
        @EndDate date = '12-31-2013'
BEGIN
SELECT H.AutoType, 
COALESCE(((SUM(H.Hours))/(CASE WHEN R.ReceivedDate BETWEEN @BeginDate AND @EndDate THEN COUNT(R.Confirmed) END)), SUM(H.Hours)) AS 'MTBUR'
FROM Hours H
INNER JOIN Repair R
ON H.SN = R.SN 
WHERE (R.Confirmed NOT LIKE 'C%' AND R.Confirmed NOT LIKE 'O%')
AND (H.Date BETWEEN @BeginDate AND @EndDate) 
GROUP BY H.AutoType, 
R.ReceivedDate
END

The following are example results for 2 types:

Type | MTBUR
------------
a    | value
a    | value
a    | value
b    | value
b    | value
b    | value

I want my results to look like this:

Type | MTBUR
------------
a    | value
b    | value

Why is it grouping the same type several times. I want only 1 value for each type.

Also, Why is the DBMS making me also group by ReceivedDate? I get the feeling that is screwing my results up. Any suggestions?

The following are my CREATE TABLE:

CREATE TABLE [dbo].[acss_hours](
    [hoursId] [uniqueidentifier] NOT NULL,
    [name] [nvarchar](100) NULL,
    [Type] [nvarchar](100) NULL,
    [SN] [nvarchar](100) NULL,
    [Reg] [nvarchar](100) NULL,
    [Hours] [float] NULL,
    [Date] [datetime] NULL)

CREATE TABLE [dbo].[repair](
    [repairId] [uniqueidentifier] NOT NULL,
[Part] [nvarchar](100) NULL,
    [Customer] [nvarchar](100) NULL,
    [AutoType] [nvarchar](100) NULL,
    [ReceivedDate] [datetime] NULL,
    [Confirmed] [nvarchar](100) NULL,
    [Company] [nvarchar](100) NULL,
    [Reg] [nvarchar](100) NULL,
    [Manu] [nvarchar](100) NULL,
    [SN] [nvarchar](100) NULL)

Vulcronos · Accepted Answer · 2014-07-08 20:38:15Z

2

You are correct, adding ReceivedDate is screwing up your results. You are getting one row for each type for RecievedDate.

SQL Server if forcing you to add RecievedDate to the group by because you are using it in the select clause. When SQL Server processes each AutoType, what ReceivedDate should it use? It has multiple ReceivedDates per AutoType. Either it needs to use each seperate ReceivedDate by adding it to the group by, or it can use a aggregate function like min or max to select one of the RecievedDates.

How do you want your query to handle it?

I think you should wrap your case in the COUNT.

COUNT(CASE WHEN R.ReceivedDate BETWEEN @BeginDate AND @EndDate 
THEN R.Confirmed ELSE 0 END)

answered Jul 8, 2014 at 20:38

Vulcronos

3,4563 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

J.S.Orris Over a year ago

This was the answer I was looking for. I deduced that it was grouping my autotype by as many times there is a dateReceived. Thanks

datamodel · Accepted Answer · 2014-07-08 20:16:10Z

1

You need to include R.ReceivedDate from your calculation in the group by because you're evaluating the column with the between statement. Its the same as including the column in the select. Basically any column in the select line that doesn't have an aggregation function needs to be in the group by.

answered Jul 8, 2014 at 20:16

datamodel

1167 bronze badges

2 Comments

J.S.Orris Over a year ago

How could I reword my script so do not have to add ReceivedDate in my Group By clause? I think that's what is causing me problems.

datamodel Over a year ago

Try using an inner query... something like this:

Satwik Nadkarny · Accepted Answer · 2014-07-08 20:12:33Z

0

You will have to make use of the keyword DISTINCT.

So effectively, you can query as such:

DECLARE @BeginDate date = '01-01-2013', 
        @EndDate date = '12-31-2013'
BEGIN
SELECT       DISTINCT H.AutoType, 
             COALESCE(((SUM(H.Hours))/(CASE WHEN R.ReceivedDate BETWEEN @BeginDate AND @EndDate THEN COUNT(R.Confirmed) END)), SUM(H.Hours)) AS 'MTBUR'

FROM         Hours H
INNER JOIN   Repair R ON H.SN = R.SN 
WHERE        (R.Confirmed NOT LIKE 'C%' AND R.Confirmed NOT LIKE 'O%')
AND          (H.Date BETWEEN @BeginDate AND @EndDate) 
GROUP BY     H.AutoType, R.ReceivedDate
END

Hope this helps!!!

answered Jul 8, 2014 at 20:12

Satwik Nadkarny

5,1432 gold badges26 silver badges41 bronze badges

3 Comments

J.S.Orris Over a year ago

I used DISTINCT previously, although it produces less results for the same type, it still produces multiple results for the same type. Would you recommend putting the case statement in the beginning of the query?

Satwik Nadkarny Over a year ago

@JeffOrris I can't seem to understand the "it still produces multiple results for the same type" part. Could you please post the table structure and sample data for these two tables in order to make it easier for us to understand?

J.S.Orris Over a year ago

The results should be distinct car types with their own MTBUR calculation. Im still getting multiple results of the same car type with differenct MTBUR calcs..If you look at my first result example set from the question, it is still looking like that.

datamodel · Accepted Answer · 2014-07-08 20:25:57Z

0

Didn't let me post this in the comment. Try an inner query:

SELECT H.AutoType, COALESCE(((SUM(H.Hours))/(SUM(x.CountConfirmed))), SUM(H.Hours)) AS 'MTBUR'
FROM 
    (SELECT H.AutoType, CASE WHEN R.ReceivedDate BETWEEN @BeginDate AND @EndDate THEN 1 ELSE 0 END AS CountConfirmed
    FROM Hours H
    INNER JOIN Repair R
    ON H.SN = R.SN 
    WHERE (R.Confirmed NOT LIKE 'C%' AND R.Confirmed NOT LIKE 'O%')
    AND (H.Date BETWEEN @BeginDate AND @EndDate)) x
JOIN Hours H
ON H.AutoType = x.AutoType
WHERE (H.Date BETWEEN @BeginDate AND @EndDate) 
GROUP BY H.AutoType

answered Jul 8, 2014 at 20:25

datamodel

1167 bronze badges

2 Comments

J.S.Orris Over a year ago

I like that idea. Was going to try that but didn't know if it would make a diff

J.S.Orris Over a year ago

I posted the CREATE TABLE scripts if it would be any more help

Collectives™ on Stack Overflow

SQL Aggregate Function Query not Producing Expected Results

4 Answers 4

1 Comment

2 Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related