GROUP BY MONTH gives wrong result

  • A+
Category:Languages

I am trying to get unique products views grouped by month using the following query

SELECT   Month(timestamp)           AS 'month',   COUNT(DISTINCT visitor_id) AS 'unique' FROM productviews pv INNER JOIN products p ON pv.product_id = p.id WHERE p.vendor_id = 8 AND YEAR(timestamp) = 2018 GROUP BY month(timestamp); 

And I am getting

+---------+--------+ | month   | unique | +---------+--------+ |    1    |  3     | +---------+--------+ |    2    |  10    | +---------+--------+ |    3    |  2     | +---------+--------+ |    4    |  4     | +---------+--------+ 

But the total number of unique product views is less than the sum of the previous query

SELECT count(DISTINCT pv.visitor_id) FROM productviews pv INNER JOIN products p ON pv.product_id = p.id WHERE p.vendor_id = 8 AND year(timestamp) = 2018  +---------+ |  count  | +---------+ |    16   | +---------+ 

Is it the right way to use group by month? Or Do I miss something?

 


Your query is OK.

Because a visitor can have been visiting more than one month.

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: