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
– Suhel Meman, Commented Jan 29, 2013 at 10:02
@AkamOmer i tried in that way but still getting the same result.what todo.thank — tradebel123
– tradebel123, Commented Jan 29, 2013 at 10:06
@SuhelMeman mate i am getting same result in another way..any other subjection...thanx — tradebel123
– tradebel123, Commented Jan 29, 2013 at 10:08

hsan · Accepted Answer · 2013-01-29 10:05:18Z

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, 2013 at 10:05

hsan

1,5591 gold badge9 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1646111 Over a year ago

@hasan: just needs group by name at this case :)

tradebel123 Over a year ago

wow thank god... halo mate you save my day..thanks that was perfect.... you are genius

hsan Over a year ago

@AkamOmer true due to MySQL allowing ambiguous group by queries if it is not in ONLY_FULL_GROUP_BY mode. I chose play it safe and to stick to the SQL standard :)

user1646111 Over a year ago

its just like view (selecting from view) will be the trick also

Devang Rathod · Accepted Answer · 2013-01-29 10:03:19Z

1

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

GROUP BY c.name

answered Jan 29, 2013 at 10:03

Devang Rathod

6,7342 gold badges25 silver badges32 bronze badges

Comments

Suhel Meman · Accepted Answer · 2013-01-29 10:05:10Z

0

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)

answered Jan 29, 2013 at 10:05

Suhel Meman

3,8621 gold badge20 silver badges27 bronze badges

Collectives™ on Stack Overflow

PHP MYSQL Query to combine and add two equalent result

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related