CODEWITHSUNDEEP
sqlsql-server
Richard F.
Richard F.

Reputation: 67

SQL server: Addition based on multiple columns

I have a table of order information called Demands with data that would look something like this:

Invoice | Product | Quantity | Route
————————|—————————|——————————|——————————
2254619 | A       | 10       | 20160112
2254619 | B       | 5        | 20160112
2254619 | C       | 4        | 20160112
2254619 | D       | 7        | 20160112
2254619 | E       | 3        | 20160112
2254808 | A       | 8        | 20160112
2254808 | B       | 2        | 20160112
2254808 | C       | 9        | 20160112
2254808 | D       | 0        | 20160112
2254808 | E       | 11       | 20160112
2254902 | A       | 7        | 20160113
2254905 | A       | 4        | 20160113

What I need is a query that will calculate the total Quantity for ALL Products for every Route.

So, the results would show that Route 20160112 has a Quantity of 18 of Product A, 7 of Product B, etc. and that Route 20160113 has 11 Product A, etc.

And this will be for multiple routes and many different products.

Any help you guys can provide would be immensely appreciated.

Upvotes: 1

Views: 51

Answers (2)

Charlie Jung
Charlie Jung

Reputation: 81

Oops, I missed the previous post that obviously is exactly the same as mine.

Wouldn't "GROUP BY" work?

SELECT Product, Route, SUM(Quantity) FROM table GROUP BY Product, Route;

SUM() applies to records of a "group" defined by Product and Route.

Upvotes: 0

Paul
Paul

Reputation: 1167

Group rows and use aggregate functions

SELECT Route, Product, SUM(Quantity)
FROM   Demands
GROUP BY Route, Product

Upvotes: 4

Related Questions