GROUP BY MONTH gives wrong result

excel pivot table group dates by month and year
sort pivot table columns by month and year
cannot group dates in pivot table
how do i sort dates in pivot table chronologically
how to group by month
excel pivot table group by month not working
mysql group by not returning all results
min group by sql

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.

USMLE Step 2 CK Lecture Notes 2017: Psychiatry, Epidemiology, , Of all the answer choices, the data provided on the confidence interval for the carboplatin resistant graph provides the best evidence that the statement is inaccurate. random variation in outcome incidence among the study groups (​Tazofect-study and The statement makes reference to the number of months of PFS in  I am trying to get unique products views grouped by month using the following queryAnd I am gettingBut the total number of unique product views is less than the sum

If a visitor viewed a product in 2 different months, then the first query will count it twice, since the visitor_id will be unique within those two months. However, the 2nd query will count these views only once, since the visitor_id is duplicated across the entire year.

So, nothing wrong with the results of either of the queries.

Current Housing Reports: American housing survey for the , The survey estimates them by assuming that they are like some group of homes that of American Housing Survey for the U. S. in 1995 gives the completeness rates for 1995. to the same question, usually from the same respondents, a month apart. Wrong answers create wrong results and mean that data on groups (for  If you change the GROUP BY clause to only group by year, then the sum calculated is the sum of all products for the year. In this query result set, the sum of revenue is the same for each row corresponding to a given year, and that sum represents the total sales for that year.

It’s clear, if you count the distinct products without dates, you get 16. However, if you count the distinct products by month, you can have the same product in different months, so the final number won’t match. If you just count (without distinct) the final number is gonna be the same I.E. The count distinct for fruits are 3, orange, apple, banana. If I count distinct fruits by month I can have orange and apple in January, orange and banana in February, orange, apple and banana in March...

American housing survey for the Minneapolis-St. Paul metropolitan , The survey estimates them by assuming that they are like some group of Table 2 in Appendix D of American Housing Survey for the U. 5. in 1995 gives the completeness rates for 1995. question, usually from the same respondents, a month apart. Wrong answers create wrong results and mean that data on groups (for  Re: Sum of values by month and by year. yes absolutely clear. Please add calculated columns for Month and Year, Month Number is optional but I recommend to add this too. Then create your Measur as Sum of the Value Column, and put Year and Month in PivotTable rows and your measure in the value field.

GROUP BY gives wrong result with MIN() aggregate function , As a MySQL DBA, I sadly admit that MySQL can be rather cavalier in its SQL processing. One of the most infamous feats of this is its GROUP BY behavior. The OVER and PARTITION BY functions are both functions used to portion a results set according to specified criteria. This article explains how these two functions can be used in conjunction to retrieve partitioned data in very specific ways. To execute our sample queries, let’s first create a database named “studentdb”.

IMF Staff Papers, Volume 49, No. 1, 2002, However, the process allows for a monthly revision of a prediction by one's forecast gives weight to how successful the forecaster performs relative to others rather than is more relevant and, as a result, move to the wrong target as a group. The GROUP BY clause groups records into summary rows. GROUP BY returns one records for each group. GROUP BY typically also involves aggregates: COUNT, MAX, SUM, AVG, etc. GROUP BY can group by one or more columns.

Days in between two dates group by month, EndDate. The intermediate results: Below is the query that shows some of the intermediate column values: SELECT EndDate)) x (end_dt) ) end_dt (dt) GROUP BY dd. I am sure it can be fix if its giving wrong output for other sample data. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause. Syntax. The basic syntax of a GROUP BY clause is shown in the following code block. The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.

Comments
  • If I had to guess, I'd say you have some overlap with visitor_id and each month. In the first query, visitors are unique per month. In the second, it's across the entire year so the sum will be the same or smaller
  • I guess it is not the cause, but in the first query you use DISTINCT visitor_id instead DISTINCT pv.visitor_id...
  • @DaltonCézane probably not important. If the column's table was ambiguous, MySQL would throw an error
  • Is there any proposal to get the true query for this? month name, number of unique visitor for this month
  • But your queries are both correct. Why would it be wrong? And what do you want insterad?
  • When a client investigates the numbers, he will find a difference between the total and the visit per day. maybe it is still not clear to me
  • Think about a visitor that is visiting january and february. He is in both month counts. So he is counted twice if you add this together. But If you count the visitors of the complete year he will be counted only once. That is the difference.