SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Can this query with two tables be combined?

    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!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, you can combine them

    hint: use a UNION query

    give it a try and let me know if you have any problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!
    Convert your dollars into silver coins. www.convert2silver.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!
    Convert your dollars into silver coins. www.convert2silver.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    my bad

    i thought your column called `date` had, you know, dates in it
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Convert your dollars into silver coins. www.convert2silver.com

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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
    Convert your dollars into silver coins. www.convert2silver.com

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, that got all messed up

    i find colour coding sometimes helps
    Code:
    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...
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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'
    Convert your dollars into silver coins. www.convert2silver.com

  14. #14
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    r937, are you on vacation?

    :-)
    Convert your dollars into silver coins. www.convert2silver.com

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks good brother, I found the problem. I forgot for my newest table that I used loginDate instead of just "date".
    Convert your dollars into silver coins. www.convert2silver.com

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •