CODEWITHSUNDEEP
sqlsql-servert-sql
Juan Le Roux
Juan Le Roux

Reputation: 89

TSQL query with aggregated data

I have a query that returns the min, max, avg and total for two different products. The client however wants the min, max, avg and total combined for both products. However I have an undesired result returned when I try to sum the two products and try to aggregate the date.

The following SQL query returns the individual data for both product:

SELECT 
   CONVERT(date, DateTimeNow) AS Date,
   MIN(FT1) AS Minimum1 , 
   MAX(FT1) AS Maximum1, 
   AVG(FT1) AS Average1, 
   MAX(FQ1) AS Total1,
   MIN(FT2) AS Minimum2, 
   MAX(FT2) AS Maximum2, 
   AVG(FT2) AS Average2, 
   MAX(FQ2) AS Total2
FROM Data
GROUP BY CONVERT(date, DateTimeNow)
ORDER BY CONVERT(date, DateTimeNow);

I then modified the query to the following:

SELECT 
    CONVERT(date, DateTimeNow) AS Date,
    MIN(FT1+FT2) AS Minimum , 
    MAX(FT1+FT2) AS Maximum, 
    AVG(FT1+FT2) AS Average, 
    MAX(FQ1+FT2) AS Total
FROM Data
GROUP BY CONVERT(date, DateTimeNow)
ORDER BY CONVERT(date, DateTimeNow);

However when I run the second query the results don't add up. As an example Minimum does not add up to the first query Minimum1 + Minimum2

Upvotes: 1

Views: 54

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

I would strongly suggest:

SELECT CONVERT(date, DateTimeNow) AS Date,
       MIN(v.FT) AS Minimum , 
       MAX(v.FT) AS Maximum, 
       AVG(v.FT) AS Average, 
       MAX(v.FQ1) AS Total
FROM Data CROSS APPLY
     (VALUES (FT1, FQ1), (FT2, FQ2)) as v(FT, FQ)
GROUP BY CONVERT(date, DateTimeNow)
ORDER BY CONVERT(date, DateTimeNow);

In particular, this should handle NULL values correctly. The average of two values is not necessarily the average of the averages of the two values.

Upvotes: 1

GMB
GMB

Reputation: 222462

I understand that you are storing each product in a different column in the same table. The logic to compute the combined minimum, average and maximum might be as follows :

SELECT 
    CONVERT(date, DateTimeNow) AS Date,
    CASE WHEN MIN(FT1) < MIN(FT2) THEN MIN(FT1) ELSE MIN(FT2) END AS Minimum, 
    CASE WHEN MAX(FT1) > MAX(FT2) THEN MAX(FT1) ELSE MAX(FT2) END AS Maximum, 
    ( AVG(FT1) + AVG(FT2) ) / 2 AS Average,
    SUM(FT1) + SUM(FT2) AS Total
FROM Data
GROUP BY CONVERT(date, DateTimeNow)
ORDER BY CONVERT(date, DateTimeNow);

Meaning :

  • minimum is the lowest value between the minimum of product 1 and the minimum of product 2
  • maximum is the highest value between the maximum of product 1 and the maximum of product 2
  • average is the average of the average of product 1 and the average of product 2
  • total is the sum of both queries

You can adapt these rules as needed according to your requiremens (which you did not fully specified).

Upvotes: 1

Juan Le Roux
Juan Le Roux

Reputation: 89

Thanks to everyone for your input. Below was the correct query I was after:

SELECT 
    CONVERT(date, DateTimeNow) AS Date, MIN(FT1) + MIN(FT2) AS Minimum, 
    MAX(FT1) + MAX(FT2) AS Maximum,
    AVG(FT1) + AVG(FT2) / 2 AS Average,
    MAX(FQ1) + MAX(FQ2) AS Total1
FROM #rawData
GROUP BY CONVERT(date, DateTimeNow)
ORDER BY CONVERT(date, DateTimeNow);

Upvotes: 1

Sql Surfer
Sql Surfer

Reputation: 1422

The "FROM" Clause does the adding by including both records you want to do the operations against. Your Date logic is not doing anything - it is grabbing all records for all days and then "falsely" putting today's date always.

SELECT CONVERT(date, DateTimeNow) AS Date, MIN(FT) AS Minimum , MAX(FT) AS Maximum, AVG(FT) AS Average, SUM(FT) AS Total, COUNT(FT) as NumOfRecs
FROM Data
WHERE ProductName IN ('FTName1','FTName2')
GROUP BY CONVERT(date, DateTimeNow)
ORDER BY CONVERT(date, DateTimeNow);

Upvotes: 0

Related Questions