SQL server: Addition based on multiple columns

Question

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.

Paul · Accepted Answer · 2016-01-12 19:54:22Z

4

Group rows and use aggregate functions

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

answered Jan 12, 2016 at 19:54

Paul

1,16714 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Richard F. Over a year ago

That did it. Thank you!

Charlie Jung · Accepted Answer · 2016-01-12 21:03:53Z

0

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.

answered Jan 12, 2016 at 21:03

Charlie Jung

814 bronze badges

Collectives™ on Stack Overflow

SQL server: Addition based on multiple columns

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related