Somebody please help me to solve my sql query i have already spend two days for this....
i have a MYSQL query given below
(SELECT
c.cl_list as cl_list,
c.name as name,
pc.value as value,
count( pc.value) as total
FROM
projs p
LEFT JOIN classify_proj_new pc
ON p.proj_id = pc.proj_id_fk
LEFT JOIN classify_list c
ON c.cl_list = pc.class_id_fk
WHERE
MATCH ( p.title ) AGAINST ( 'jerm' IN BOOLEAN MODE )
GROUP BY
c.cl_list,
pc.value)
UNION ALL
(SELECT
c.cl_list as cl_list,
c.name as name,
pc.value as value,
count( pc.value) as total
FROM
jerm p
LEFT JOIN classify_jerm_new pc
ON p.jerm_id = pc.jerm_id_fk
LEFT JOIN classify_list c
ON c.cl_list = pc.class_id_fk
WHERE
MATCH ( p.jermname ) AGAINST ( 'jerm' IN BOOLEAN MODE )
GROUP BY
c.cl_list,
pc.value)
Which Gives a result of (below):
cl_list name value total
------------------------------------------------------------------------------------
1 department jewller 2
3 price 50 2
6 color blue 1
6 color Red 2
1 department jewller 1
6 color Red 1
but i am trying to get a result which can add the repeating value's total and avoid repeating value....some thing like this (below):
cl_list name value total
------------------------------------------------------------------------------------
1 department jewller 3
3 price 50 2
6 color blue 1
6 color Red 3
somebody please help me i am very sad about my output...
Thank you very much in advance...