CODEWITHSUNDEEP
phpmysqlgroup-by
roy
roy

Reputation: 141

Php Mysql Multiple Group By Results in Single Line

I have data structure Below enter image description here

I need results with group by country and group by count results like below enter image description here

I have tried below code it is

$sql = "SELECT Country, 
     (SELECT COUNT(*) FROM   ip_ptc WHERE type='view' and ad_id=" . $db->real_escape_string($input->gc['aid']) .") as view,
     (SELECT COUNT(*) FROM   ip_ptc WHERE type='interactions' and ad_id=" . $db->real_escape_string($input->gc['aid']) .") as interactions,
     (SELECT COUNT(*) FROM   ip_ptc WHERE type='click' and ad_id=" . $db->real_escape_string($input->gc['aid']) .") as click,
        ROUND(100.0*COUNT(ip)/(SELECT COUNT(*) FROM ip_ptc WHERE ad_id=" . $db->real_escape_string($input->gc['aid']) ."), 2) as percentage
    FROM ip_ptc 
    WHERE ad_id=" . $db->real_escape_string($input->gc['aid']) . 
    
   " GROUP BY Country
    UNION ALL
    SELECT 'Total Clicks' ip, COUNT(ip) as sum,COUNT(ip) as sum,COUNT(ip) as sum,'100.00'
    
    FROM ip_ptc
    WHERE ad_id=" . $db->real_escape_string($input->gc['aid']);

it is showing same count results for views,interactions and clicks like below.

enter image description here

I know this is because country is not passed in count statement but getting no idea how to do this

Upvotes: 1

Views: 129

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

You may use conditional aggregation here along with ROLLUP:

SELECT
    COALESCE(Country, 'Total Clicks') AS Country,
    SUM(type = 'view') AS views,
    SUM(type = 'interactions') AS interactions,
    SUM(type = 'click') AS clicks
FROM ip_ptc
WHERE
    ad_id = ?
GROUP BY
    Country WITH ROLLUP;

Upvotes: 1

Related Questions