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

Answer

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).