getting multi row data into columns in mysql

Question

SELECT 
  Day, 
  month, 
  year,
  GROUP_CONCAT(total), 
  GROUP_CONCAT(SP_ID)
FROM
(
    SELECT 
      DATE_FORMAT(l.act_date, '%d') AS DAY, 
      DATE_FORMAT(l.act_date, '%M') AS MONTH,
      EXTRACT(YEAR FROM l.act_date) AS YEAR, 
      COUNT(*) as total,l.sp_id 
    FROM lead_activity2 as l
    right outer join salesperson as s on l.sp_id=s.sp_id
    WHERE l.act_name='scb' 
      AND ((l.act_date>='2012-09-07 13:03:27' ) 
      AND (l.act_date<= '2012-11-07 13:03:27')) 
    GROUP BY MONTH, YEAR, DAY, l.sp_id 
    ORDER BY YEAR DESC, MONTH DESC, DAY DESC, l.sp_id DESC
) t GROUP BY day, month, year

http://sqlfiddle.com/#!2/1514d/3 - you can view the scheme and the query,

what i would like to get is

18 | october | 2012 | 0,0,1,1 | 6,5,4,3

spid 6 and spid 5 have no data for 18 october but still should be shown tried doing right join and right outer join both dont seem to work...

Use GROUP_CONCAT(IFNULL(total, 0)) to get 0 for rows with no matching data in the outer join. — Barmar
– Barmar, Commented Nov 7, 2012 at 9:51
@barmer can you write the query and show it ... i tired putting an outer join ... " outer join salesperson as s on l.sp_id=s.sp_id" and it gives an error — Apurva
– Apurva, Commented Nov 7, 2012 at 10:31

Mahmoud Gamal · Accepted Answer · 2012-11-07 11:33:32Z

1

Use GROUP_CONCAT like so:

SELECT 
  Day, 
  month, 
  year,
  GROUP_CONCAT(total), 
  GROUP_CONCAT(SP_ID)
FROM
(
    SELECT 
      DATE_FORMAT(l.act_date, '%d') AS DAY, 
      DATE_FORMAT(l.act_date, '%M') AS MONTH,
      EXTRACT(YEAR FROM l.act_date) AS YEAR, 
      COUNT(*) as total,l.sp_id 
    FROM lead_activity2 as l 
    WHERE l.act_name='scb' 
      AND ((l.act_date>='2012-09-07 13:03:27' ) 
      AND (l.act_date<= '2012-11-07 13:03:27')) 
    GROUP BY MONTH, YEAR, DAY, l.sp_id 
    ORDER BY YEAR DESC, MONTH DESC, DAY DESC, l.sp_id DESC
) t GROUP BY day, month, year

Updated SQL Fiddle

Update: Yes you can do this, but use LEFT JOIN to include non matching sp_id. These non matching ids will have a value of NULL use IFNULL to display it with zeros like so:

SELECT 
  Day, 
  month, 
  year,
  GROUP_CONCAT(total) Total, 
  GROUP_CONCAT(SP_ID) 'List of sp_ids'
FROM
(
  SELECT
    DATE_FORMAT(l.act_date, '%d') AS DAY, 
    DATE_FORMAT(l.act_date, '%M') AS MONTH, 
    EXTRACT(YEAR FROM l.act_date) AS YEAR, 
    COUNT(*) as total,
    IFNULL(s.sp_id , 0) sp_id
  FROM lead_activity2 as l 
  LEFT JOIN salesperson s ON l.sp_id = s.sp_id
  WHERE l.act_name='scb' 
    AND ((l.act_date>='2012-09-07 13:03:27' )
    AND (l.act_date<= '2012-11-07 13:03:27')) 
  GROUP BY MONTH, YEAR,DAY,s.sp_id 
) t
ORDER BY YEAR DESC, 
         MONTH DESC, 
         DAY DESC, 
         sp_id DESC

Updates SQL Fiddle Demo

edited Nov 7, 2012 at 11:33

answered Nov 7, 2012 at 9:20

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

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

2 Comments

Apurva Over a year ago

this works.... however just wanted to know if i had a join statement to another table which contained details of sp_id ...and say i had 5 different sp_id in that table... how can the query be modified ...to add the sp_id's that not match / get hit on the above query and add a 0 for example 18 | october | 2012 | 0,1,1,0,0 | 5,4,3,2,1

Apurva Over a year ago

hey the result is a bit off it should give lal dates for SCB and lal the users including null...

Collectives™ on Stack Overflow

getting multi row data into columns in mysql

1 Answer 1

Updated SQL Fiddle

Updates SQL Fiddle Demo

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related