John Davidson

Php Mysql Multiple Group By Results in Single Line


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
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
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

Rate this post

3 of 5 based on 7817 votes


© 2024 - Personal Blogs Platform. All Rights Reserved.
Create blog  |  Privacy Policy  |  Terms & Conditions  |  Contact Us