php mysql sum issue

Question

I want to generate a report to accumulate the total stock quantity that has been ordered by different branches in a month, and each branch will sum up the quantity by merged (GROUP BY) stock.

i trimmed up my database table for easy understanding as below:

Item     Branch        Order_Quantity
---------------------------------------
Pencil   Branch-A         5
Ruler    Branch-D         3
Staple   Branch-C         12
Pencil   Branch-A         5
Ruler    Branch-B         3
Staple   Branch-C         2
Pencil   Branch-A         10
Ruler    Branch-A         6
Staple   Branch-D         1

for example, below is draft of expected outcome result:

Item     Branch-A     Branch-B     Branch-C     Branch-D
----------------------------------------------------------
Pencil     20            15           32           8
Ruler      12            0            40           10
Staple     4             8             5           0

and so on...

How can I use query to call the above result and assign each sum to their respective branch column?

below is my query:

SELECT `Item`, `Branch`, sum(`Order_Quantity`) FROM `table` GROUP BY `Item`

but when I call and loop the table, the result will show sum quantity to every branches

Item     Branch-A     Branch-B     Branch-C     Branch-D
----------------------------------------------------------
Pencil     75            75           75           75
Ruler      62            62           62           62
Staple     17            17           17           17

Hope someone can help for this.

thanks.

what exactly are you "loop"ing? You need to paste in your php because there's nothing in your sql that would generate separate columns of branches as you have them displayed. — Yevgeny Simkin
– Yevgeny Simkin, Commented Jan 4, 2012 at 6:56

Ayman Safadi · Accepted Answer · 2012-01-04 06:58:14Z

3

Try:

SELECT `Item`, `Branch`, sum(`Order_Quantity`) FROM `table` GROUP BY `Item`, `Branch`

answered Jan 4, 2012 at 6:58

Ayman Safadi

11.6k1 gold badge30 silver badges42 bronze badges

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

Comments

Michał Powaga · Accepted Answer · 2012-01-04 07:04:19Z

1

This is some kind of PIVOT:

select item, 
    sum(case when branch = 'Branch-A' then order_Quantity else 0 end) as BranchA,
    sum(case when branch = 'Branch-B' then order_Quantity else 0 end) as BranchB,
    sum(case when branch = 'Branch-C' then order_Quantity else 0 end) as BranchC,
    sum(case when branch = 'Branch-D' then order_Quantity else 0 end) as BranchD
from yourTable
group by item

answered Jan 4, 2012 at 7:04

Michał Powaga

23.4k8 gold badges55 silver badges62 bronze badges

Comments

Yevgeny Simkin · Accepted Answer · 2012-01-04 08:09:47Z

1

assuming you have a finite number of branches you can create a query with sub selects for each "column" you wish to display (as per branch) with your sums, like so...

select 
  (select sum(order_quantity) from table where branch = 'Branch-A'),
  (select sum(order_quantity) from table where branch = 'Branch-B'),

etc..

This is not a very elegant solution, but it will result in what you're looking for.

Otherwise you can follow the advice of the other posters which is more efficient from a sql standpoint but will not generate the columns you are hoping to see (as per your question).

edited Jan 4, 2012 at 8:09

answered Jan 4, 2012 at 7:00

Yevgeny Simkin

28.6k41 gold badges145 silver badges243 bronze badges

5 Comments

Egor Sazanovich Over a year ago

Why do You used LIKE if string is a full match?

Yevgeny Simkin Over a year ago

force of habit. Always compare strings with like and numbers with =. For me it's just easier to look at. I'm not sure if there's any efficiency loss, if there is, I've never heard of it.

Egor Sazanovich Over a year ago

@DrDredel it uses regular expressions, that so it's not a good habit to compare with LIKE when You needn't LIKE

Yevgeny Simkin Over a year ago

@EgorSazanovich, huh... ok, good to know, thanks! (I edited my answer, though I still think my approach is a terrible one, and is only "right" in that it addresses the specific request of the OP.

Egor Sazanovich Over a year ago

@DrDredel sometimes You need a fast solution, not strict, but fast ;) Have a good day!

Egor Sazanovich · Accepted Answer · 2012-01-04 06:57:01Z

0

SELECT SUM(Order_Quantity) AS CNT FROM `table` GROUP BY `Branch`;

answered Jan 4, 2012 at 6:57

Egor Sazanovich

5,1295 gold badges27 silver badges37 bronze badges

Collectives™ on Stack Overflow

php mysql sum issue

4 Answers 4

Comments

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related