Psd Psd - 4 months ago 10
MySQL Question

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.

Answer

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;