CODEWITHSUNDEEP
mysql
Ryan Kempt
Ryan Kempt

Reputation: 4209

MySQL - Complex Query w/ GROUP_CONCAT... Single Table

I have the following table:

id | billingno | location
-------------------------
1  | 9999999   | Toronto
2  | 9999999   | Toronto
3  | 7777777   | Toronto
4  | 7777777   | Quebec

I need a query that would generate me something that looked like this:

location | total | display
--------------------------
Toronto  | 3     | 9999999 - 2, 7777777 - 1
Quebec   | 1     | 7777777 - 1

So, it groups by location, displays the total number of billingno's for that location, and then the display column lists each billingno and how many times they were in that location. I have been trying to write this for some time, my closest attempt is this:

SELECT location, COUNT(*) AS total, GROUP_CONCAT(DISTINCT CAST(CONCAT(CONVERT(billingno,CHAR(16)), ' - ', THIS_COUNT_PART_FOR_EACH_LOCATION_IN_DISPLAY_DOESNT_WORK)AS CHAR)
SEPARATOR ' - ') AS display
FROM table GROUP BY location
ORDER BY COUNT(*) DESC

It gives me everything I need except I cannot for the life of me figure out how to count the number of each billingno's under display. If I use COUNT() it gives me an error about grouping. Please help!

Oh, I also had to use the convert to char so it would show up as text and not a BLOB in phpMyAdmin. Thanks again!

Upvotes: 0

Views: 901

Answers (3)

piotrekkr
piotrekkr

Reputation: 3206

SELECT location, SUM( total ) AS total, GROUP_CONCAT( CONCAT( billingno, ' - ', billing_count ) ) AS display
FROM (

SELECT location, COUNT( billingno ) AS total, billingno, COUNT( billingno ) AS billing_count
FROM billing
GROUP BY location, billingno
ORDER BY COUNT( * ) DESC 
) AS t
GROUP BY location

Upvotes: 0

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

How about this,

SELECT table.location, 
       SUM(LocationCount) AS Total,  
       GROUP_CONCAT(CAST(CONCAT(CONVERT(billingno,CHAR(16)), ' - ', THIS_COUNT_PART_FOR_EACH_LOCATION_IN_DISPLAY_DOESNT_WORK)AS CHAR)
SEPARATOR ' - ') AS display
FROM table 
     LEFT JOIN 
     (SELECT location , COUNT(id) AS LocationCount
      FROM table
      GROUP BY location) t on t.location = table.location
GROUP BY location
ORDER BY SUM(LocationCount) DESC

Upvotes: 0

Michał Powaga
Michał Powaga

Reputation: 23183

Sample data:

create table location (
    id int,
    billingno varchar(10),
    location varchar(10)
);

insert into location
select 1, '9999999', 'Toronto' union 
select 2, '9999999', 'Toronto' union 
select 3, '7777777', 'Toronto' union 
select 4, '7777777', 'Quebec' ;

Query:

select 
    location, 
    sum(qty) as total, 
    group_concat(concat(billingno, ' - ', cast(qty as char(7))) 
        order by qty desc separator ', '
    ) as display
from (
    select billingno, location, count(*) as qty
    from location
    group by  billingno, location
) t
group by location
order by total desc

Result:

location    total   display
Toronto     3       9999999 - 2, 7777777 - 1
Quebec      1       7777777 - 1

Upvotes: 1

Related Questions