34191

Group by day with filled gaps

Question:

I am trying to generate a daily sales reports for a particular user based on this tutorial <a href="http://www.richnetapps.com/using-mysql-generate-daily-sales-reports-filled-gaps/" rel="nofollow">Using MySQL to generate daily sales reports with filled gaps</a>. To do this, I have three tables, <strong>records table</strong>, <strong>user table</strong> and <strong>calendar table</strong>

records user calendar id id datefield user_id timestamp

The query below returns 0 as total and NULL as the user_id when data is not available for a particular day which is great:

SELECT calendar.datefield AS DATE, IFNULL(COUNT(records.id),0) AS total, records.user_id FROM records RIGHT JOIN calendar ON (DATE(records.timestamp) = calendar.datefield) WHERE ( calendar.datefield BETWEEN (NOW() - INTERVAL 14 DAY) AND NOW() ) GROUP BY DATE DESC

The idea is to generate this report for a particular user so I modified the above query to what follows:

SELECT calendar.datefield AS DATE, IFNULL(COUNT(records.id),0) AS total, records.user_id FROM records RIGHT JOIN calendar ON (DATE(records.timestamp) = calendar.datefield) WHERE ( calendar.datefield BETWEEN (NOW() - INTERVAL 14 DAY) AND NOW() AND records.user_id = SOME_EXISTING_USER_ID ) GROUP BY DATE DESC

This return an empty result when there is no record but the idea is to return 0 for any particular day which does not have data.

How can I modify the first query to work for a particular user?

Answer1:

Wow. Been a while since I've seen a RIGHT JOIN in the wild! Anyway, try adding the user predicate from the WHERE clause into the RIGHT JOIN like this:

SELECT calendar.datefield AS DATE, IFNULL(COUNT(records.id),0) AS total, records.user_id FROM records RIGHT JOIN calendar ON (DATE(records.timestamp) = calendar.datefield) AND records.user_id = SOME_EXISTING_USER_ID WHERE ( calendar.datefield BETWEEN (NOW() - INTERVAL 14 DAY) AND NOW() ) GROUP BY DATE DESC;

For me this is one of the great benefits of explicit joins vs implicit joins...

Recommend

  • Google service account can't impersonate GSuite user
  • How to get current events from google calendar API?
  • How to specify a multi-column UNIQUE constraint in code-first Entity Framework fluent API
  • How to request unsampled report from Google Analytics Management API using Google Apps Script?
  • SQL: Getting the physical size of a subset of a table
  • Identifier too long in Oracle
  • How to plot large time series (thousands of administration times/doses of a medication)?
  • Easiest way to get current unix timestamp via XSL
  • HttpListener.IsSupported is false on XP SP3
  • pip in virtualenv gets ConnectTimeoutError
  • How to get data from **Realm database** using **date object**?
  • Saving image to sd with current date and time in name doesn't work
  • How to make JSON.NET deserialize to Microsoft Date Time?
  • Combining two different ActiveRecord collections into one
  • Stop Bash Script if Hive Fails
  • Marklogic : Query response time is very high
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • Why querying a date BC is changed to AD in Java?
  • Row Count Is Returning the incorrect number using RaptureXML
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Illegal mix of collations for operation for date/time comparison
  • Fill an image in a square container while keeping aspect ratio
  • Display Images one by one with next and previous functionality
  • Release, debug version and Authorization Google?
  • How to get next/previous record number?
  • Rearranging Cells in UITableView Bug & Saving Changes
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Linker errors when using intrinsic function via function pointer
  • File upload with ng-file-upload throwing error
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • json Serialization in asp
  • SQL merge duplicate rows and join values that are different
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • Hits per day in Google Big Query
  • How do you join a server to an Active Directory (domain)?
  • How to stop GridView from loading again when I press back button?
  • How does Linux kernel interrupt the application?
  • LevelDB C iterator
  • Reading document lines to the user (python)
  • How can i traverse a binary tree from right to left in java?