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;”;
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?
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
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.
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
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?
i thought your column called date had, you know, dates in it
SELECT [COLOR="Blue"]date_no_time[/COLOR]
, SUM(s) AS Sender
, SUM(x) AS Searches
FROM ( SELECT [COLOR="blue"]DATE(`date`) AS date_no_time[/COLOR]
, COUNT(*) AS s
, 0 AS x
FROM referrals
GROUP
BY [COLOR="blue"]date_no_time[/COLOR]
UNION ALL
SELECT [COLOR="blue"]DATE(`date`) AS date_no_time[/COLOR]
, 0
, COUNT(*)
FROM searches
GROUP
BY [COLOR="blue"]date_no_time[/COLOR] ) AS u
GROUP
BY [COLOR="blue"]date_no_time[/COLOR]
ORDER
BY [COLOR="blue"]date_no_time[/COLOR] DESC LIMIT 10
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.
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
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
SELECT date_no_time
, [COLOR="Red"]SUM(s) AS Sender[/COLOR]
, [COLOR="DarkGreen"]SUM(x) AS Searches[/COLOR]
, [COLOR="Blue"]SUM(l) AS Logins[/COLOR]
FROM ( SELECT DATE(`date`) AS date_no_time
, [COLOR="red"]COUNT(*) AS s[/COLOR]
, [COLOR="darkgreen"]0[/COLOR] AS x
, [COLOR="blue"]0[/COLOR] AS l
FROM referrals
GROUP
BY date_no_time
UNION ALL
SELECT DATE(`date`) AS date_no_time
, [COLOR="red"]0[/COLOR]
, [COLOR="darkgreen"]COUNT(*)[/COLOR]
, [COLOR="blue"]0[/COLOR]
FROM searches
GROUP
BY date_no_time
UNION ALL
SELECT DATE(`date`) AS date_no_time
, [COLOR="red"]0[/COLOR]
, [COLOR="darkgreen"]0[/COLOR]
, [COLOR="blue"]COUNT(*)[/COLOR]
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 [COLOR="red"]12[/COLOR] [COLOR="darkgreen"]0[/COLOR] [COLOR="blue"]0[/COLOR]
2010-11-14 [COLOR="red"]0[/COLOR] [COLOR="darkgreen"]23[/COLOR] [COLOR="blue"]0[/COLOR]
2010-11-14 [COLOR="red"]0[/COLOR] [COLOR="darkgreen"]0[/COLOR] [COLOR="blue"]5[/COLOR]
2010-11-15 [COLOR="red"]11[/COLOR] [COLOR="darkgreen"]0[/COLOR] [COLOR="blue"]0[/COLOR]
2010-11-15 [COLOR="red"]0[/COLOR] [COLOR="darkgreen"]9[/COLOR] [COLOR="blue"]0[/COLOR]
2010-11-15 [COLOR="red"]0[/COLOR] [COLOR="darkgreen"]0[/COLOR] [COLOR="blue"]37[/COLOR]
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
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’
