CODEWITHSUNDEEP
sqlsql-serversumsubquerymax
Alessandro
Alessandro

Reputation: 13

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

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       |

Upvotes: 1

Views: 146

Answers (2)

Sai Abhiram Inapala
Sai Abhiram Inapala

Reputation: 359

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I think you want ORDER BY and TOP:

SELECT TOP (1) 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
ORDER BY o.DATE DESC;

If you would want multiple rows if there were ties, you would use TOP (1) WITH TIES.

Upvotes: 1

Related Questions