46802

Retrieve records using JOIN query

Question:

I want to retrieve data of last 1 week from emp_info table on per day basis.

So I have used :

SELECT DAYNAME(timestamp), COUNT(*) FROM `emp_info` WHERE DATE(timestamp ) > DATE_SUB(CURDATE( ) , INTERVAL 1 WEEK ) GROUP BY DAYNAME(timestamp);

According to the query I am getting result like:

Monday 5 Thursday 7

But I also want the <strong>result</strong> of weekday as <strong>0</strong> on which no record has been entered.

From suggestions I come to know about <strong>JOIN</strong> query. So I have tried to fix it but not getting any solution.

Answer1:

The result you are getting is right because there are no records on a specific dayname. Since you want to get all daynames, you need to project complete set of day (<em>using UNION inside a SUBQUERY</em>) and join it with your existing query.

SELECT a.day_name, COALESCE(b.totalCount, 0) totalCount FROM ( SELECT 'Sunday' day_name, 1 ordby UNION ALL SELECT 'Monday' day_name, 2 ordby UNION ALL SELECT 'Tuesday' day_name, 3 ordby UNION ALL SELECT 'Wednesday' day_name, 4 ordby UNION ALL SELECT 'Thursday' day_name, 5 ordby UNION ALL SELECT 'Friday' day_name, 6 ordby UNION ALL SELECT 'Saturday' day_name, 7 ordby ) a LEFT JOIN ( SELECT DAYNAME(timestamp) day_name, COUNT(*) totalCount FROM `emp_info` WHERE DATE(timestamp ) > DATE_SUB(CURDATE( ) , INTERVAL 1 WEEK ) GROUP BY DAYNAME(timestamp) ) b ON a.day_name = b.day_name ORDER BY a.ordby <ul><li><a href="http://www.sqlfiddle.com/#!2/3d813/1" rel="nofollow">SQLFiddle Demo (<em>simple example</em>)</a></li> </ul>

Recommend

  • fetch data grouped by month using mysql interval
  • Set MySQL Column Alias to a Calculated Date
  • Using Mysql function in java spring boot
  • LINQ to Entities does not recognize the method
  • PHP create range of dates
  • Full calendar business hour constrain on day click event
  • dayname(curdate()) NOT working in codeigniter php
  • Drupal: create a node with employee working hours
  • WCF Service Application returns 404 using WebGet
  • function cannot execute on segment because it accesses relation
  • AngularJs ng-repeat filtering by a deeper tier of data
  • Determining if a string has been entered with a leading single quote
  • Type mismatch: cannot convert from Connection to Connection
  • Drag and Drop for imageview not working
  • Is it possible to disable certain weekdays in DatePickerDialog?
  • Get or convert Week of year to ISO week
  • MySql - get days remaining
  • data.table replicate rows after join?
  • Runtime.exec() gives Error: Could not find or load main class
  • Sending keystrokes/mouse clicks to a Java program with Autohotkey
  • How to match http request and response using Jersey ContainerRequestFilter and ContainerResponseFilt
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Django: Count of Group Elements
  • Read text file and split every line in MSBuild
  • Different response to non-authenticated users and AJAX calls
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • How to check if every primary key value is being referenced as foreign key in another table
  • Knitr HTML Loop - Some HTML output, some R output
  • How to handle AllServersUnavailable Exception
  • Display Images one by one with next and previous functionality
  • How to get next/previous record number?
  • php design question - will a Helper help here?
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • SQL merge duplicate rows and join values that are different
  • AngularJs get employee from factory
  • embed rChart in Markdown
  • Change div Background jquery
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Authorize attributes not working in MVC 4