select with count in sub select gives unwanted result due to group by clause

Question

SELECT t.id_type, brand, model, countMod FROM types t  
    LEFT JOIN (
       SELECT p.id_type, brand, model, COUNT( model ) AS countMod 
           FROM products p  
           GROUP BY brand) p2 
    ON t.id_type = p2.id_type 
    WHERE t.id_type = "TCE" 
    ORDER BY brand

This query works alright but I lose the models which are grouped, of course, under each brand.
Is there a way to have the models listed and counted?
I.e. brand1, model1, model2, model3, countMod=3 -- brand2, model1, model2, countMod=2.
Any help will be greatly appreciated. Thanks

Answer 1

SELECT brand, GROUP_CONCAT(model) models, COUNT(model) AS countMod
FROM products
GROUP BY brand

Answer 2

try this::

SELECT 
t.id_type, 
brand,
GROUP_CONCAT(model SEPARATOR ',') as model, 
COUNT( model ) AS countMod 
FROM types t
LEFT JOIN products p  ON t.id_type = p.id_type
WHERE t.id_type = "TCE" 
GROUP BY brand
ORDER BY brand

Answer 3

The recommended approach is the run two queries, one for the data and one with COUNT().

With MySQL, you can also use SQL_CALC_FOUND_ROWS (mostly if you have a LIMIT clause), however the performance of that is disputable, see Which is fastest? SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*).