I have the next query: That is working perfectly and returning 9 rows: I want to group the results by boat type and get the number of boats per type. However, when I do: I´m getting: but according to the first query, I´m expecting What am I missing? Answer I suspect that you want: That is: move the DISTINCT within

Group by not getting the expected results in mysql

I have the next query:

SELECT DISTINCT
    bt.name, b.id
FROM
    ports po,
    cities c,
    provinces p,
    countries co,
    states s,
    translations t,
    element_types et,    
    languages l,
    boat_models bm, 
    boat_types bt,
    boats b
        JOIN
    boat_prices bprf ON b.id = bprf.boat_id
        AND bprf.checkin_date IS NULL
        AND bprf.duration_id IS NULL
WHERE
    t.element_translation = 'España'
    AND et.name = 'Country'
    AND s.name = 'confirmed'    
    AND s.id = b.state_id
    AND l.locale = 'es'
    AND t.language_id = l.id
    AND t.element_type_id = et.id
    AND t.element_id = p.country_id        
        AND c.province_id = p.id        
        AND po.city_id = c.id
        AND b.port_id = po.id
        AND bm.id = b.boat_model_id 
        AND bt.id = bm.boat_type_id

That is working perfectly and returning 9 rows:

'BOAT_TYPE_CATAMARAN','13707'
'BOAT_TYPE_SAILBOAT','13700'
'BOAT_TYPE_SAILBOAT','13701'
'BOAT_TYPE_SAILBOAT','13702'
'BOAT_TYPE_SAILBOAT','13703'
'BOAT_TYPE_SAILBOAT','13704'
'BOAT_TYPE_SAILBOAT','13705'
'BOAT_TYPE_SAILBOAT','13706'
'BOAT_TYPE_SAILBOAT','13708'

I want to group the results by boat type and get the number of boats per type.

However, when I do:

SELECT DISTINCT
    bt.name, COUNT(b.id) AS num_boats
FROM
    ports po,
    cities c,
    provinces p,
    countries co,
    states s,
    translations t,
    element_types et,    
    languages l,
    boat_models bm, 
    boat_types bt,
    boats b
        JOIN
    boat_prices bprf ON b.id = bprf.boat_id
        AND bprf.checkin_date IS NULL
        AND bprf.duration_id IS NULL
WHERE
    t.element_translation = 'España'
    AND et.name = 'Country'
    AND s.name = 'confirmed'    
    AND s.id = b.state_id
    AND l.locale = 'es'
    AND t.language_id = l.id
    AND t.element_type_id = et.id
    AND t.element_id = p.country_id        
        AND c.province_id = p.id        
        AND po.city_id = c.id
        AND b.port_id = po.id
        AND bm.id = b.boat_model_id 
        AND bt.id = bm.boat_type_id 
GROUP BY bt.name
ORDER BY bt.name

I´m getting:

'BOAT_TYPE_CATAMARAN','241'
'BOAT_TYPE_SAILBOAT','1928'

but according to the first query, I´m expecting

'BOAT_TYPE_CATAMARAN','1'
'BOAT_TYPE_SAILBOAT','8'

What am I missing?

Answer

I suspect that you want:

SELECT bt.name, COUNT(DISTINCT b.id) AS num_boats
FROM ...
WHERE ...
GROUP BY bt.name
ORDER BY bt.name

That is: move the DISTINCT within the COUNT() rather than directly in the SELECT.

Generally speaking, DISTINCT and GROUP BY do not go along well together; DISTINCT is already aggregation in essence, so mixing both is usually not relevant.

Note that your syntax uses old-school, implicit joins (with a comma in the FROM clause): you should be using standard joins (with the ON keyword), whose syntax has been state-of-the-art for decades.

Advertisement

Answer