Can this query with two tables be combined?

[busboy]

Right now I have two different webpages that give me the data I need. But I was wondering if I could combine them onto one report. Here are the two queries I use:

$dailySearches = "SELECT DATE_FORMAT(date, '%Y-%m-%d') as Date, COUNT(*) as Searches FROM searches GROUP BY DATE_FORMAT(date, '%Y-%M-%D') order by Date desc limit 120;";


$dailyLogins = "SELECT DATE_FORMAT(last, '%Y-%m-%d') as Date, COUNT(*) as Logins FROM users GROUP BY DATE_FORMAT(last, '%Y-%M-%D') order by Date desc limit 120;";

Thanks!

[r937]

yes, you can combine them

hint: use a UNION query

give it a try and let me know if you have any problem

[busboy]

Are you sure a union will work in this situation? I read online that for a union to work, both tables must be matched. Why would anyone have two tables with the same columns? Can you help me out some more?

Thanks!

[r937]

I read online that for a union to work, both tables must be matched.

you either misunderstood it, or the source where you read it is wrong

for a union to work, both SELECTs must match

one table could have 9 columns and the other could have 37 columns, just as long as you select the same number of columns with corresponding similar datatypes

[busboy]

r937, here is what I came up with:

SELECT DATE_FORMAT(date, '%Y-%m-%d') as Date, COUNT(*) as Sender FROM referrals GROUP BY DATE_FORMAT(date, '%Y-%M-%D')
union
SELECT DATE_FORMAT(date, '%Y-%m-%d') as Date, COUNT(*) as Searches FROM searches GROUP BY DATE_FORMAT(date, '%Y-%M-%D') order by Date desc limit 10;

I'm getting closer, but not quite there. This produces a two rows for each day. One of the rows is for the counted number of referrals sent and the other row contains the number searches conducted. Can you help be get this sorted out? I would like one row for each date, and one column for referrals and another column for searches.

Thank you!

[r937]

SELECT `date`
     , SUM(s) AS Sender 
     , SUM(x) AS Searches
  FROM ( SELECT `date`
              , COUNT(*) AS s 
              , 0        AS x
           FROM referrals 
         GROUP 
             BY `date`
         UNION ALL
         SELECT `date`
              , 0 
              , COUNT(*)
           FROM searches 
         GROUP
            BY `date` ) AS u
GROUP          
    BY `date`  
ORDER
    BY `date` DESC LIMIT 10

[busboy]

Ok, we are getting closer, but not quite there. I now have lots of rows for 2010-11-09. The sender column is nothing but zeros and the searches column all contain 1. For some reason it doesn't appear to have grouped by date. Can you help some more?

Thanks!

[r937]

my bad

i thought your column called `date` had, you know, dates in it

SELECT date_no_time
     , SUM(s) AS Sender 
     , SUM(x) AS Searches
  FROM ( SELECT DATE(`date`) AS date_no_time
              , COUNT(*) AS s 
              , 0        AS x
           FROM referrals 
         GROUP 
             BY date_no_time
         UNION ALL
         SELECT DATE(`date`) AS date_no_time
              , 0 
              , COUNT(*)
           FROM searches 
         GROUP
            BY date_no_time ) AS u
GROUP          
    BY date_no_time  
ORDER
    BY date_no_time DESC LIMIT 10

[busboy]

Wow, that is one of the most complex SQL queries I have ever seen. You have a gift from God! Would it be too much trouble to show me how to tweak this to have one more column? I would also like to include a column called, "Logins". The needed table is called "users" and the column in that table that contains their last login date is called, "last".

This will allow me to have a single page which details the activities of the day in terms of logins, searches and referrals sent.

Thank you, Sir.

[r937]

okay, here's how to add another column

in the subquery, where the UNION is, you will need to add another SELECT

and in each of the (now) 3 SELECTs, you have to add a 3rd column, and this is the part where you must be careful, this new 3rd column will be 0 in the first two SELECTs

then in the outer query, add another SUM

give it a try and if you have trouble i will correct it for you

[busboy]

Ok, I took at stab at it. Unfortunately it didn't work. Here is what I came up with:

SELECT date_no_time
, SUM(s) AS Referrals
, SUM(l) AS Logins
, SUM(x) AS Searches
FROM ( SELECT DATE(`date`) AS date_no_time
, COUNT(*) AS s
, COUNT(*) AS l
, 0 AS x
FROM referrals
GROUP
BY date_no_time
UNION ALL
SELECT DATE(`date`) AS date_no_time
, 0
, 0
, COUNT(*)
FROM searches
GROUP
BY date_no_time ) AS u
SELECT DATE(`date`) AS date_no_time
, 0
, 0
, COUNT(*)
FROM users
GROUP
BY date_no_time ) AS l
GROUP
BY date_no_time
ORDER
BY date_no_time DESC LIMIT 10

[r937]

yeah, that got all messed up

i find colour coding sometimes helps

SELECT date_no_time
     , SUM(s) AS Sender
     , SUM(x) AS Searches
     , SUM(l) AS Logins
  FROM ( SELECT DATE(`date`) AS date_no_time
              , COUNT(*) AS s
              , 0        AS x
              , 0        AS l
           FROM referrals
         GROUP
             BY date_no_time
         UNION ALL
         SELECT DATE(`date`) AS date_no_time
              , 0
              , COUNT(*)
              , 0
           FROM searches
         GROUP
            BY date_no_time
         UNION ALL
         SELECT DATE(`date`) AS date_no_time
              , 0
              , 0
              , COUNT(*)
           FROM users
         GROUP
            BY date_no_time ) AS u
GROUP
    BY date_no_time
ORDER
    BY date_no_time DESC LIMIT 10

it may also help to visualize the rows produced by the UNION...

date_no_time   s   x   l
2010-11-14    12   0   0
2010-11-14     0  23   0
2010-11-14     0   0   5
2010-11-15    11   0   0
2010-11-15     0   9   0
2010-11-15     0   0  37

yours was the 3rd SELECT in the UNION, so the 3rd column (blue) in each SELECT

the outer query produces the SUMs of the three rows for each date

[busboy]

I did some slight changing to the query, now referring to the logins table instead of the users table. Somehow I'm getting an error now, which will be shown below my query.

Thanks

SELECT date_no_time
, SUM(r) AS Referrals
, SUM(s) AS Searches
, SUM(l) AS Logins
FROM ( SELECT DATE(`date`) AS date_no_time
, COUNT(*) AS r
, 0 AS s
, 0 AS l
FROM referrals
GROUP
BY date_no_time
UNION ALL
SELECT DATE(`date`) AS date_no_time
, 0
, COUNT(*)
, 0
FROM searches
GROUP
BY date_no_time
UNION ALL
SELECT DATE(`date`) AS date_no_time
, 0
, 0
, COUNT(*)
FROM logins
GROUP
BY date_no_time) AS l
GROUP
BY date_no_time
ORDER
BY date_no_time DESC LIMIT 60;";

Error:
#1054 - Unknown column 'date' in 'field list'

[busboy]

r937, are you on vacation?

:-)

[r937]

no, i'm not on vacation

your error message says you don't have a column `date` in one of those tables

want me to come over to your place and find which table it is?

[busboy]

Thanks good brother, I found the problem. I forgot for my newest table that I used loginDate instead of just "date".

[r937]

I forgot for my newest table that I used loginDate instead of just "date".

so i guess you were the one that was on vacation, eh