PHP SQL get unique values from column-CodePudding

I want to create to modify sql query so it will choose all unique values (it's integer) from column traffic_type. There is a part of code that should be edited:

    $statistic_query = array(
        'select' => array(
            
            'SUM(installs) AS installs_count',
            'SUM(fake_installs) AS fake_installs_count',
            'SUM(searchs) AS searchs_count',
            'SUM(clicks) AS clicks_count',
            'AVG(bid) AS bid_avg',
            'SUM(user_profit) AS user_profit_sum',
            'SUM(system_profit) AS system_profit_sum',
            'SUM(visits) AS visits_count',
            'SUM(downloads) AS downloads_count',
            'SUM(DISTINCT traffic_type) AS traffic_type_count'
        ),
        'from' => ' statistic'
    );

It's working but I don't need to sum unique values I want to get all unique values separated with commas like 0, 2. When I'm changing 'SUM(DISTINCT traffic_type) AS traffic_type_count' to 'DISTINCT traffic_type AS traffic_type_count' - there is an error:

Error 500
CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT traffic_type) AS traffic_type_count, `dt_add` FROM `statistic` WHERE (d' at line 1

It can be because it gets an array, not number. So is there a way to get all unique values separated with commas like one variable, not an array?

CodePudding user response：

When you want to get comma separated list of values use GROUP_CONCAT

GROUP_CONCAT(traffic_type) AS traffic_type_count

Also you can use DISTINCT within for filter only unique values

GROUP_CONCAT(DISTINCT traffic_type) AS traffic_type_count

online SQL editor