Mysql count occurrence by group as a column value

Question

I've made the following query:

select ba_brand, ceil(sum((sk_front + sk_depth + sk_warehouse + (sk_exhibition  IS NOT NULL))) / count(distinct(vi_pdv_id_pdv))) as 'inventory',
count(vi_pdv_id_pdv)
from sf_visit_stock

join sf_visit on sk_vi_id_visit = id_visit
join sf_pdv on vi_pdv_id_pdv = id_pdv
join sf_format on pdv_fo_id_format = id_format
join sf_group on fo_gr_id_group = id_group
join sf_channel on gr_ch_id_channel = id_channel

join sf_product on sk_pd_id_product = id_product
join sf_family on pd_fa_id_family = id_family
join sf_brand on fa_ba_id_brand = id_brand

where (CASE WHEN from_unixtime(vi_scheduled_start,'%Y-%m-%d') between '2014-10-01' and '2014-10-31' AND vi_vs_id_visit_status in (1,2,3,4,6) THEN 4 END)
and vi_pr_id_proyect = 5 and ba_rival = 0 and id_brand in (72,75,76,77,78,79,80)
and sf_channel.ch_channel not in ("CHAINS DIRECTS", "CHAINS INDIRECTS", "INDEPENDENTS", "SUPERMARKET")

group by ba_brand

In other words, I'm doing a sum() of some values:

sum((sk_front + sk_depth + sk_warehouse + (sk_exhibition  IS NOT NULL))

From a number of rows:

count(vi_pdv_id_pdv)

But the IDs in these rows are duplicated, so count(vi_pdv_id_pdv) returns me a value counting all those duplicated IDs. I'd like to get a value of those vi_pdv_id_pdv without couinting duplicates. Here goes a little example. With this query I'm getting the following result:

My Product |    32031 | 1098

In this example 1098 counts duplicated values. And I'l like to get:

My Product |    32031 | 534

That count is without duplicated values. I know about the group by statement to count without duplicates like in this answer I've read, but I'm not able to implement something similar to that in the query I've shown you above.

You should alias all your tables. It's difficult to understand your joins as we don't know the names of the columns in your tables. You will also avoid problems if two tables were to have a column with the same name. — Pan, Jan 11 '20 at 11:53

score 0 · Accepted Answer · answered Oct 30 '14 at 23:09

0

You could use COUNT(DISTINCT expr,[expr...])

http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_count-distinct

answered Oct 30 '14 at 23:09

Kescha Skywalker

479
4
9

I had already tried this but I did `count(distinct vi_pdv_id_pdv)` and it's not the same. THANKS – Metafaniel Oct 31 '14 at 17:15

score 0 · Answer 2 · answered Jan 11 '20 at 11:50

Where do the duplicates come from?

I assume they are caused by one of your joins. You could add additional clauses to this join to get rid of them. Example:

SELECT *
FROM sf_visit_stock AS vs
JOIN sf_visit AS v ON vs.sk_vi_id_visit = v.id_visit AND v.somevalue = 1

It may also be possible to get rid of them in the WHERE clause.

Mysql count occurrence by group as a column value

2 Answers2