John Davidson

Message:

I have data structure Below

I need results with group by country and group by count results like below

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.

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

---

My Answer:
SELECT country, COUNT(*) as total_count
FROM table_name
GROUP BY country;

But this will give results in multiple lines like below:

Country | Total Count
-----------------------
USA | 10
Canada | 5
UK | 8

If you want the results in a single line with multiple group by results, you can use GROUP_CONCAT function in MySQL like below:

$sql = "SELECT GROUP_CONCAT(country) as countries, GROUP_CONCAT(total_count) as total_counts
FROM (
SELECT country, COUNT(*) as total_count
FROM table_name
GROUP BY country
) as subquery";

This will give you results in a single line like below:

Countries | Total Counts
--------------------------------
USA, Canada, UK | 10, 5, 8