MySql:while using GROUP_CONCAT & COUNT

Question

I have a database in which there is a user table & how many time they reward record table I wanna result like this (WEEK WISE)

user_name user_week user_reward
abc          24,25,26    4,5,6

My query is like this

SELECT WEEK(cpd.added_date),COUNT(cpd.result)
FROM cron_players_data cpd WHERE cpd.player_id = 81
AND cpd.result = 2 AND cpd.status = 1
GROUP BY WEEK(cpd.added_date);

And Result is

user_name user_week user_reward
abc          24    4
abc          24    5
abc          24    6

i use group_concat with count but its useless is there any alternate method for this desire result

The user_* columns do not appear in your query.

Explosion Pills
– Explosion Pills

2012-06-28 05:43:05 +00:00
Commented Jun 28, 2012 at 5:43 — Explosion Pills
– Explosion Pills, Commented Jun 28, 2012 at 5:43

Puggan Se · Accepted Answer · 2012-06-28 06:01:32Z

1

You have 2 level grouping, first week, and then username, so i sugest you use a subquery for that first grouping like:

SELECT
 user_name,
 GROUP_CONCAT(user_week) AS user_weeks, 
 GROUP_CONCAT(user_reward) AS user_rewards
FROM (
  SELECT
   user_name,
   WEEK(cpd.added_date) AS user_week,
   COUNT(cpd.result) AS user_reward
  FROM cron_players_data AS cpd 
  WHERE
   cpd.player_id = 81 AND
   cpd.result = 2 AND
   cpd.status = 1
  GROUP BY user_week
) as temp
GROUP BY user_name;

answered Jun 28, 2012 at 6:01

Puggan Se

5,8462 gold badges25 silver badges51 bronze badges

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

Collectives™ on Stack Overflow

MySql:while using GROUP_CONCAT & COUNT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related