GROUP_CONCAT in MySQL - how do I include distinct values?

Question

I have two tables which I query with UNION ALL - one for Android and one for iPhone. Each table has its own device field ("android" or "iphone").

I use GROUP_CONCAT(DISTINCT `device` ORDER BY `device` SEPARATOR ', ') AS `device` a few times, each time when I group by another column (such as date, user_id etc.). I also use the same GROUP_CONCAT to calculate totals.

The problem is, when I group by the date, I select GROUP_CONCAT of the device (not the device directly) because there are some dates where there are both Android and iPhone purchases. The select also includes WHERE or HAVING to let the user filter by specific dates, the number of users who joined in a date etc. When I calculate the totals I have GROUP_CONCAT on the device, which is itself a GROUP_CONCAT function. The result can be something like "android, android, iphone, iphone" because there are dates with only Android, dates with only iPhone and dates with both (other queries can have only one or some of these options). I'm looking for a way to convert this result to "android, iphone".

Currently I'm using a PHP function:

    private function get_device_human_string($fp_device_computer_string)
    {
        $devices= array(
            'android' => 'Android',
            'iphone' => 'iPhone'
        );
        $device_computer_string= strtolower($fp_device_computer_string);
        $ret= array();
        foreach ($devices as $device_key => $device_human_string)
        {
            if (strpos($device_computer_string, $device_key) !== false)
            {
                $ret[]= $device_human_string;
            }
        }
        return implode(', ', $ret);
    }

But I'm looking for a way to do it in MySQL (the returned result should also have a capital A in "Android" and a capital P in "iPhone", but I don't mind to use the PHP function for that).

By the way, the total SELECT query is a SELECT .... FROM (SELECT .... FROM (.... UNION ALL ....) GROUP BY ....) and the GROUP BY is inside. If I don't include the device in the inner query then there will be no device to GROUP_CONCAT in the outer query. So I can't GROUP_CONCAT on the device directly.

Edit: This is an example of the query I'm using (the WHERE and HAVING can change according to user's filters):

SELECT
  COUNT(1) AS `count`,
  SUM(`joined`) AS `joined`,
  SUM(`users`) AS `users`,
  SUM(`purchases`) AS `purchases`,
  SUM(`credits_purchased`) AS `credits_purchased`,
  GROUP_CONCAT(DISTINCT `device` ORDER BY `device` SEPARATOR ', ') AS `device`,
  GROUP_CONCAT(DISTINCT `application` ORDER BY `application` SEPARATOR ', ') AS `application`
FROM (
  SELECT
    `all_purchases`.*,
    IF(`users_joined`.`joined` IS NOT NULL, `users_joined`.`joined`, 0) AS `joined`,
    'esalne' AS `application`
  FROM (
    SELECT
      DATE(`date`) AS `date`,
      COUNT(DISTINCT `user_id`) AS `users`,
      COUNT(1) AS `purchases`,
      SUM(
        IF(
          STRCMP(SUBSTRING(`item`, 1, CHAR_LENGTH('esalne.sip.')), 'esalne.sip.')=0,
          CAST(SUBSTRING(`item`, CHAR_LENGTH('esalne.sip.')+1) AS UNSIGNED INTEGER),
          0
        )
      ) AS `credits_purchased`,
      GROUP_CONCAT(DISTINCT `device` ORDER BY `device` SEPARATOR ', ') AS `device`
    FROM (
      (
        SELECT 
         `id`,
          `item`,
          `date`,
          `status`,
          `user` AS `user_id`,
          NULL AS `transaction_id`,
          'android' AS `device`
        FROM `enswitch_android_purchases`
        WHERE (`status`=1)
          AND (`user` IS NOT NULL)
      )
      UNION ALL
      (
        SELECT
          `id`,
          `item`,
          `date`,
          `status`,
          `user` AS `user_id`,
          `transaction_id`,
          'iphone' AS `device`
        FROM `enswitch_iphone_purchases`
        WHERE (`status`=1)
          AND (`user` IS NOT NULL)
      )
    ) AS `all_purchases`
    GROUP BY DATE(`date`)
  ) AS `all_purchases`
  LEFT JOIN (
    SELECT
      `join_date` AS `date`,
      COUNT(1) AS `joined`
    FROM (
      SELECT
        `user_id`,
        MIN(DATE(`date`)) AS `join_date`
      FROM (
        (
          SELECT
            `id`,
            `item`,
            `date`,
            `status`,
            `user` AS `user_id`,
            NULL AS `transaction_id`,
            'android' AS `device`
          FROM `enswitch_android_purchases`
          WHERE (`status`=1)
            AND (`user` IS NOT NULL)
        )
        UNION ALL
        (
          SELECT
            `id`,
            `item`,
            `date`,
            `status`,
            `user` AS `user_id`,
            `transaction_id`,
            'iphone' AS `device`
          FROM `enswitch_iphone_purchases`
          WHERE (`status`=1)
            AND (`user` IS NOT NULL)
        )
      ) AS `all_purchases`
      GROUP BY `user_id`
    ) AS `users`
    GROUP BY `date`
  ) AS `users_joined` ON (`all_purchases`.`date`=`users_joined`.`date`)
  HAVING (`date` >= DATE_ADD('2012-11-01', INTERVAL 0 DAY))
     AND (`date` < DATE_ADD('2012-11-30', INTERVAL 1 DAY))
     AND (`joined` >= 2)
     AND (`purchases` <= 30)
     AND (`credits_purchased` <= 3000)
) AS `all_purchases_by_dates`

Thanks, Uri.

Noone can help you if you don't post the actual query. – ypercubeᵀᴹ Dec 16 '12 at 16:55 — ypercubeᵀᴹ, Dec 16 '12 at 16:55

score 0 · Answer 1 · answered Dec 16 '12 at 16:59

Just a draft to illustrate an idea:

select d1.device, d2.device, ...
from (
    select 'android' as device
    union
    select 'iphone' as device 
    ) as devices
left outer join mydata d1 on d1.device = devices.device and devices.device = 'android'
left outer join mydata d2 on d2.device = devices.device and devices.device = 'iphone'
inner join ...
where ...

using the 'dummy' table and the outer joins you can get all records for android and iphone so that either both devices are non-null or at least one of them is non-null. You can concatenate d1.device and d2.device if you like or evaluate them in any other suitable way to group by these.

GROUP_CONCAT in MySQL - how do I include distinct values?

1 Answers1