CODEWITHSUNDEEP
mysqlselect
Psd
Psd

Reputation: 25

Group Concat in mysql statement

I've got a table called delitems with some colums.

Within my SELECT statement I want to use a GROUP_CONCAT: 

 +-------------------------------+-------+--------+--------+-----+
 | COLOR                         | tOTAL | Ptotal | Amount | qty |
 +-------------------------------+-------+--------+--------+-----+
 | BLUE - W = 55,BLUE - W/O = 93 |   148 |    375 |  55500 |   2 |
 +-------------------------------+-------+--------+--------+-----+
 mysql>select GROUP_CONCAT(color,' = ',qty) as COLOR, SUM(qTY) AS tOTAL, suM(p_cost)  as Ptotal, SUM(qty)*SUM(p_cost) as Amount,count(*) qty from delitems where status='3' Group By cont_no;

Everything works fine except the Amount column. The total amount is wrong! Here the correct value:

+-----------------+-------+--------+--------+-----+
| COLOR           | tOTAL | Ptotal | Amount | qty |
+-----------------+-------+--------+--------+-----+
| BLUE - W = 55   |    55 |    125 |   6875 |   1 |
| BLUE - W/O = 93 |    93 |    250 |  23250 |   1 |
+-----------------+-------+--------+--------+-----+
mysql>select GROUP_CONCAT(color,' = ',qty) as COLOR, SUM(qTY) AS tOTAL, suM(p_cost) as Ptotal, SUM(qty)*SUM(p_cost) as Amount,count(*) qty from delitems where status='3' Group By color;

I only want to display it in one line with the correct total amount

Please help.

Upvotes: 1

Views: 41

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

Should be you need sum(a*b) not sum(a)*sum(b)

select 
   GROUP_CONCAT(color,' = ',qty) as COLOR
 , SUM(qTY) AS tOTAL
 , suM(p_cost)  as Ptotal
 , SUM(qty*(p_cost) as Amount, count(*) qty 
from delitems 
where status='3' Group By cont_no;

Upvotes: 1

Related Questions