CODEWITHSUNDEEP
phpmysqlsum
conmen
conmen

Reputation: 2417

php mysql sum issue

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.

Upvotes: 1

Views: 456

Answers (4)

Yevgeny Simkin
Yevgeny Simkin

Reputation: 28349

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).

Upvotes: 1

Michał Powaga
Michał Powaga

Reputation: 23183

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

Upvotes: 1

Ayman Safadi
Ayman Safadi

Reputation: 11552

Try:

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

Upvotes: 3

Egor Sazanovich
Egor Sazanovich

Reputation: 5089

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

Upvotes: 0

Related Questions