How to combine aggregate function Sum() and Max()

Question

I am working in a report to compile some results from ORDERS and ORDERS_ITEMS, trying to use SUM inside a MAX.

Please there is a better way to get the result? Maybe the key is use subqueries, but I could not achieve the correct way to do that.

Here is my simple code (http://sqlfiddle.com/#!18/5c3b88/3):

CREATE TABLE ORDERS (
    ID int NOT NULL,
    DATE datetime NULL,
    TOTAL_VALUE float(8) NOT NULL,
    PRIMARY KEY (ID)
);

INSERT INTO ORDERS (ID, DATE, TOTAL_VALUE) VALUES
(245, '2020-11-23 10:37:28.467' , 1848.06),
(246, '2020-11-23 12:06:07.157' , 281.45),
(247, '2020-11-23 12:06:49.250' , 84.06),
(248, '2020-11-23 14:45:58.347' , 59.8);

CREATE TABLE ORDERS_ITEMS (
    ITEM_ID int NOT NULL,
    ID int NOT NULL,
    QUANTITY float(8) NOT NULL,
    PRIMARY KEY (ITEM_ID)
);

INSERT INTO ORDERS_ITEMS (ITEM_ID, ID, QUANTITY) VALUES
(1 , 245 , 10),
(2 , 245 , 3),
(3 , 245 , 8),
(4 , 245 , 4),
(5 , 245 , 50),
(6 , 246 , 5),
(7 , 247 , 1),
(8 , 248 , 2);

This:

SELECT O.ID, O.DATE, O.TOTAL_VALUE, OI.QUANTITY
FROM ORDERS O
INNER JOIN ORDERS_ITEMS OI ON O.ID = OI.ID
WHERE O.DATE >= (GetDate() - 90); -- SELECT last 90 days

Returns this:

ID  | DATE                    | TOTAL_VALUE | QUANTITY |
245 | 2020-11-23 10:37:28.467 | 1848.06     | 10       |
245 | 2020-11-23 10:37:28.467 | 1848.06     | 3        |
245 | 2020-11-23 10:37:28.467 | 1848.06     | 8        |
245 | 2020-11-23 10:37:28.467 | 1848.06     | 4        |
245 | 2020-11-23 10:37:28.467 | 1848.06     | 50       |
246 | 2020-11-23 12:06:07.157 | 281.45      | 5        |
247 | 2020-11-23 12:06:49.250 | 84.06       | 1        |
248 | 2020-11-23 14:45:58.347 | 59.8        | 2        |

And this does not give the real result ( 2273.37 ) from O.TOTAL_VALUE:

SELECT
Max(O.ID), Max(O.DATE), Max(O.TOTAL_VALUE), Sum(OI.QUANTITY)
FROM ORDERS O
INNER JOIN ORDERS_ITEMS OI ON O.ID = OI.ID
WHERE O.DATE >= (GetDate() - 90) -- SELECT last 90 days
GROUP BY O.ID;

Please could you help me to achieve this result?

ID  | DATE                    | TOTAL_VALUE    | QUANTITY |
248 | 2020-11-23 14:45:58.347 | 2273.37        | 83       |

Answer 1

I see that you are trying to get sum of all orders and not individual orders. In that case why do you need order ID and date in the final output?

The following query is what you are asking for.

    SELECT (select max(ID) from ORDERS) as ID,
       (select max(DATE) from ORDERS) as DATE,
       SUM(TOTAL_VALUE),
       SUM(QUANTITY)
FROM (
SELECT distinct O.ID
              , O.DATE
              , max (O.TOTAL_VALUE) TOTAL_VALUE
              , SUM (OI.QUANTITY ) over(partition by O.ID) QUANTITY
FROM ORDERS O
INNER JOIN ORDERS_ITEMS OI ON O.ID = OI.ID
WHERE O.DATE >= (GetDate() - 90) -- SELECT last 90 days
group by O.ID,OI.QUANTITY,O.DATE
)A