PHP MYSQL Query to combine and add two equalent result

Question

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

`group` the result on name and value, instead of cl_list and value, — Suhel Meman, Jan 29 '13 at 10:02
@AkamOmer i tried in that way but still getting the same result.what todo.thank — tradebel123, Jan 29 '13 at 10:06
@SuhelMeman mate i am getting same result in another way..any other subjection...thanx — tradebel123, Jan 29 '13 at 10:08

score 3 · Accepted Answer · answered Jan 29 '13 at 10:05

3

Select from your query and group by cl_list, name and value:

SELECT
  cl_list,
  name,
  value,
  sum(total) as total
FROM (
  -- your current query here ...
) data
GROUP BY
  cl_list,
  name,
  value

answered Jan 29 '13 at 10:05

hsan

1,560
1
9
12

@hasan: just needs group by name at this case :) – Jan 29 '13 at 10:08
wow thank god... halo mate you save my day..thanks that was perfect.... you are genius – tradebel123 Jan 29 '13 at 10:19
@AkamOmer true due to MySQL allowing ambiguous group by queries if it is not in [`ONLY_FULL_GROUP_BY`](http://stackoverflow.com/questions/1645921/mysql-group-by-behavior) mode. I chose play it safe and to stick to the SQL standard :) – hsan Jan 29 '13 at 10:21
its just like view (selecting from view) will be the trick also – Jan 29 '13 at 10:32

score 1 · Answer 2 · answered Jan 29 '13 at 10:03

1

try below code. it will display unique record and avoid duplication.

GROUP BY c.name

answered Jan 29 '13 at 10:03

Devang Rathod

6,650
2
23
32

score 0 · Answer 3 · answered Jan 29 '13 at 10:05

group the result on name and value

(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.name, 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.name, pc.value)

PHP MYSQL Query to combine and add two equalent result

3 Answers3