retrieve data from a database the most sold products by branch

Question

I'm trying to get the most sold product by Branch on my project but I didn't know what is the problem on my Sql query.

Here is the schema of my database:

The DIM_SOUS_CAT table is the product table and each product is categorized by Branch and Category

DIM_CAT data :

DIM_BRANCHE data

DIM_SOUS_CAT data which is the product

FAIT_VENTE data, which is the sales list

I wrote a sql query but it doesn't work. here is the query:

select vf.id_branche, vf.id_categorie, count(*) 
from vente_fact vf 
GROUP by vf.id_branche, vf.id_categorie  
HAVING count(*) = (  
   SELECT max(COUNT(*)) 
   FROM vente_fact vf2  
   GROUP by vf2.id_branche, vf2.id_categorie 
) 

any suggestions please !

Answer 1

You are almost there. As far as concerns, you just need to fix the subquery:

• it needs to be correlated to the outer query

• you can't nest aggregate expressions, like MAX(COUNT(*)); that would require a additional level of aggregation - instead, you can order by and limit

I would suggest:

select 
    vf.id_branche, 
    vf.id_categorie, 
    count(*) no_ventes
from vente_fact vf 
group by vf.id_branche, vf.id_categorie 
having count(*) = (
    select count(*) 
    from vente_fact vf2 
    where vf2.id_branche = vf1.id_branche
    order by count(*) desc
    limit 1 
)

Note that if you are running MySQL 8.0, this is more efficiently done with window functions:

select id_branche, id_categorie, no_ventes
from (
    select 
        id_branche, 
        id_categorie, 
        count(*) no_ventes,
        rank() over(partition by id_branche order by count(*) desc) rn
    from vente_fact vf 
    group by id_branche, id_categorie 
) t
where rn = 1