CODEWITHSUNDEEP
sqlsql-serversum
Kart28
Kart28

Reputation: 11

SQL Sum of the same ID rows

Each service has the same price and ID. In final table I would sum quantity of the same rows - each service appear only one time and complete ID and price.

I created example table https://dbfiddle.uk/Z1EQca0b and I can only return service name and quantity

SELECT Service_name, SUM (Qty) As Sum
FROM NewOne
GROUP BY Service_Name

Value is the result of multiplying qty and price My table:

ID Service_Name Price Qty Value Ident Where
1 Repair 123.6000000000 5 618.0 123A London
2 Collect 120.1000000000 4 480.4 127B Paris
3 Buy 100.1000000000 14 1401.4 130C Paris
4 Repair 123.6000000000 5 618.0 123A Paris
5 Collect 120.1000000000 9 1080.9 127B Rome
6 Buy 100.1000000000 12 1201.2 130C Rome
7 Collect 120.1000000000 15 1801.5 127B London
8 Buy 100.1000000000 1 100.1 130C London

My expectations after query

Service_Name Price Qty Value Ident
Repair 123.6000000000 10 1236.0 123A
Collect 120.1000000000 28 3362.8 127B
Buy 100.1000000000 27 2702.7 130C

Upvotes: -1

Views: 104

Answers (3)

Drop_top
Drop_top

Reputation: 39

Use group by Service_Name, Price, and Ident while summing the Qty and Value.

SELECT 
Service_Name, 
Price, 
SUM(Qty) AS T_Qty, 
SUM(Value) AS T_Value, 
Ident
FROM NewOne
GROUP BY Service_Name, Price, Ident
ORDER BY Service_Name;

Upvotes: 0

Mike
Mike

Reputation: 59

SELECT Service_Name, Price, SUM(Qty) AS Qty, SUM(Value) AS Value, Ident
FROM NewOne
GROUP BY Service_Name, Price, Ident
ORDER BY Ident ASC

enter image description here

Upvotes: 0

Gendalph
Gendalph

Reputation: 47

SUM + GROUP BY is the way to go, so long as you have Value pre-calculated:

SELECT Service_Name, SUM(Price) AS Price, SUM(Qty) AS Qty,
    SUM(Value) AS Value, Ident
FROM NewOne
GROUP BY Service_Name, Ident

Upvotes: -2

Related Questions