Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Join 2 tables with Group By Date

I have two queries that I’m trying to combine, but I’m having an issue and I believe it has to do with the GROUP BY Date when I do LEFT JOIN. The date formats are the same and the only column I need to include into query 1 from query 2 is Qty WHERE Date = Date.

Any Help would be appreciated!

Thanks
Angel

Query 1

$dbGreeterReport = mysqli_query($conn, "SELECT COUNT(*) AS Cars, Date, SUM(Families), SUM(People), Families, People, Testimony, SUM(IF(Prayer = 'Yes', 1, 0)) AS Prayer, COUNT(DISTINCT Zipcode) AS Zipcodes    
FROM Greeter_Detail
WHERE $dateRange
GROUP BY Date");

Query 2

$dbSalv = mysqli_query($conn, "SELECT *, Date
FROM Salv
WHERE $dateRange
GROUP BY Date");

I tried Join as follows:

LEFT JOIN Greeter_Detail ON Greeter_Detail.DATE = Salv.Date

I’m looking for the results to be as follows grouped by rows by dates:

Date    Cars    Families    People  Prayer    Qty
01/06/2024  177 323 1,199   162  5
Tags:

2

Answers


  1. 0

    MySQL date/time data types include types that can be more precise than "date" e.g. 2024-01-08 does not equal 2024-01-08 17:23:47 – but you might only see "2024-01-08" on the screen. So when attempting to join data by "date" make sure both sides on the join are only date and not more precise than that. This can be done using function DATE(datecolumn) or DATE_FORMAT(datecolumn,'%y-%m-%d').

    GROUP BY queries in MySQL

    Another, very important, point to make about your queries is that they rely on a very imprecise feature of MySQL and one that you should NOT rely on.

    Imagine this

    insert into test (code1, code2, value) values
  ('a','x',100)
, ('a','y',200)
, ('a','z',300)

    and this query:

    select
     *
   , sum(value)
from test
group by
     code1

    You might expect only one row as the result, such as this:

    code1 code2 sum(value)
    a x 600

    but why is code2 "x"? After all the source row for ‘x’ only has a value of 100 so why has MySQL shown "x"?

    Answer: Because it is arbitrary (and therefore potentially misleading!), for more see this and this fiddle

    To permanently avoid these arbitrary/misleading results requires more effort by you when forming group by queries – and to help enforce this you should set ONLY_FULL_GROUP_BY ON in (all) your MySQL environment(s). Ref

    Suggested Queries

    Once you have ONLY_FULL_GROUP_BY on, you will have to be more specific about the non-aggregating columns you include in queries, for example:

    SELECT
      DATE(date) AS date
    , COUNT(*) AS Cars
    , SUM(Families)
    , SUM(People)
    , SUM(CASE WHEN Prayer = 'Yes' THEN 1 ELSE 0 END) AS Prayer
    , COUNT(DISTINCT Zipcode) AS Zipcodes
FROM Greeter_Detail
WHERE $DATERANGE
GROUP BY DATE(date)

SELECT
     DATE(date) AS date
   , SUM(Qty) AS Qty
FROM Salv
WHERE $DATERANGE
GROUP BY DATE(date)

    Note, now there are no columns where MySQL needs to choose an arbitrary value to display (e.g. select * has been replaced with SELECT DATE(date) AS date).

    Also note that now the "date" columns are more likely to find exact matches when you join the 2 results together.

    Login or Signup to reply.
  2. 0

    SELECT
    G.Date,
    COUNT(*) AS Cars,
    SUM(G.Families) AS Families_Greeter,
    SUM(G.People) AS People_Greeter,
    G.Testimony,
    SUM(IF(G.Prayer = ‘Yes’, 1, 0)) AS Prayer_Greeter,
    COUNT(DISTINCT G.Zipcode) AS Zipcodes_Greeter,
    S.Qty
    FROM Greeter_Detail G
    LEFT JOIN Salv S ON G.Date = S.Date
    WHERE $dateRange
    GROUP BY G.Date;

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search