Count sql results in table with or without values

Asked

Viewed 41 times

0

I have a table subcategorias and I have another anuncios, where it has the id of the subcategory. I need to search all subcategories and return together, by subcategory, the amount of ads it has, the problem is that I’m already generating subcategories with another loop from another table. How could I do that?

SELECT * from `subcategorias`
LEFT JOIN `anuncios` ON `subcategorias`.`id_subcategoria` = `anuncios`.`id_anuncio`
WHERE `anuncios`.`status` = 1;

So, basically, I have to list all the subcategories, and together, do a Count of how many active ads each has for later I play this loop on the page

mysql loop

  • if advertising has the id of the subcategory, why not ON subcategory.id = ads.id_subcategory ?

  • Because subcategories are generated dynamically through the categories page. By the category ID, the loop is formed with the subcategory menu. In this menu are listed the names of the subcategories and the amount of ads each has, however, even if the subcategory does not contain an ad, the loop should continue to run to bring up all subcategories but so far, what I did was bring only subcategories that contain ads.

3 answers

0

Add a COUNT(anuncios.*) AS Qtd in your query and a GROUP BY subcategory.id_subcategory. But observing thus seems to be wrong the JOIN relationship, but as the question is not that the answer above solves, thus staying:

    SELECT  
    s.*,
    count(*) as qtd
FROM subcategorias s
LEFT JOIN anuncios a
ON a.id_subcategoria = s.id_subcategoria
GROUP BY s.id_subcategoria

  • Arllondias, thanks for your help but I tried here and returned the same thing. Returns only the results related to the ads, and the important thing is to return the subcategories and, if there are ads, the amount of them.

  • I have the same tables here for testing and they work, try to change the reference of your Join that I think is wrong leaving as the answer, I will edit

0


What you can do is unite the results of categories that have no active ads with the category count that has active ads with this query:

select
subcategoria.nome,
coalesce(count(anuncios.id)) as quantidade
from subcategoria
left join anuncios on anuncios.id_subcategoria = subcategoria.id
where anuncios.status = 1
group by subcategoria.nome
union
select nome, '0' as quantidade from subcategoria
where subcategoria.id not in (select id_subcategoria from anuncios where status = 1)
order by nome

Look at this SQL Fiddle.

  • 1

    Laércio, perfect. It worked here, only now I will see how to pass to Laravel but are searching the amount of ads by subcategory. Thanks.

0

I tried to edit here to be able to pick by requested category but the quantity returned is always 1 record

I edited here to get the category requested but then the quantity always tells how to have a record 

SELECT s.*, 
COUNT(*) AS qtd 
FROM subcategorias s 
JOIN categorias c 
ON c.id_categoria = s.id_categoria 
LEFT JOIN anuncios a 
ON a.id_subcategoria = s.id_subcategoria 
WHERE s.id_categoria = 6 
GROUP BY s.id_subcategoria

Browser other questions tagged mysql loop

You are not signed in. Login or sign up in order to post.