SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,100
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Not getting the results I expected

    Hi All,
    I have a column called fb (facebook) with possible values y, n, m (yes no maybe) and a date column.
    For each date I want to get a total count of the y, n, m
    What I have is,
    (fbli = facebook login, phli is phone login)
    Code:
    SELECT ttl.fb AS tttl 
    ,COUNT(fbli.fb) AS fbli 
    ,COUNT(phli.fb) AS phli 
    ,DATE(ttl.date) AS date 
    FROM fbdata AS ttl 
    LEFT JOIN fbdata AS fbli 
    ON ttl.fb = 'y' 
    LEFT JOIN fbdata AS phli 
    ON ttl.fb = 'n' 
    WHERE ttl.nasid = :wttlnasid1 AND DATE(ttl.Date) BETWEEN :wbegin AND :wend 
    GROUP BY DATE(ttl.date)
    The query takes forever to run and the values are not correct.
    I saw an example in stackoverflow that had parentheses around the ON condition, it didn't work
    If I change ttl.date to phli.date it executes for over 30 seconds
    If I use DISTINCT in the count, everything is 3.
    I have tried throwing everything at this query.

    This has an accepted answer, which is basically what I have but doesn't work
    http://stackoverflow.com/questions/1...with-left-join

    Than you for looking and appreciate any help.
    What I lack in acuracy I make up for in misteaks

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,265
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    You're not getting the results you're looking for because those joins are bogus - you're basically getting a complete count of all records on the fbdata table for each one as you're using a LEFT JOIN and not giving any criteria to limit the values on those joined tables, so it'll pick each record.

    All you need is this query.

    Code SQL:
    SELECT DATE([DATE]) AS DATE
         , fb AS ttl
    	 , COUNT(fb) AS responseCount
      FROM fbdata
     WHERE nasid = :wttlnasid1
       AND DATE([DATE]) BETWEEN :wbegin AND :wend
       AND fb IN ('y', 'n')
     GROUP BY DATE([DATE]), fb

    You'll get a result something like this:
    Date fb responseCount
    1-Feb-2014 n 8
    1-Feb-2014 y 11
    2-Feb-2014 n 20
    3-Feb-2014 y 100


    If you REALLY want to get it all on one record per date, you could do something like this (not tested but should be close), but the performance may not be worth the processing time depending on how large you table is and how well your table is indexed.
    Code SQL:
    SELECT DATE(d.[DATE]) AS DATE
    	 , COUNT(y.[DATE]) AS yesCount
    	 , COUNT(n.[DATE]) AS noCount
      FROM fbdata d
      JOIN fbdata y ON d.nasid = y.nasid AND d.[DATE] = y.[DATE] AND y.fb = 'y'
      JOIN fbdata n ON d.nasid = y.nasid AND d.[DATE] = y.[DATE] AND n.fb = 'n'
     WHERE nasid = :wttlnasid1
       AND DATE(d.[DATE]) BETWEEN :wbegin AND :wend
     GROUP BY DATE(d.[DATE])
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,100
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Sorry for the long delay in my reply but had a shift of priorities.
    I tried your query, the second one and got extremely high numbers
    This is the query, I used d.nasid in the where clause
    Code:
    SELECT  DATE(d.date) AS DATE
     ,COUNT(y.date) AS yesCount
     ,COUNT(n.date) AS noCount
      FROM fbdata d
      JOIN fbdata y ON d.nasid = y.nasid AND DATE(d.date) = DATE(y.date) AND y.fb = 'y'
      JOIN fbdata n ON d.nasid = y.nasid AND DATE(d.date) = DATE(y.date) AND n.fb = 'n'
      WHERE d.nasid = :wdnasid1 AND DATE(d.date) BETWEEN :wbegin AND :wend GROUP BY DATE(d.date)
    I really appreciate the help, I did get your first query working and will play with that some more.

    It looks the the second query is what I'm after though, did I do something wrong?.

    Thank you very much and appreciate your help
    What I lack in acuracy I make up for in misteaks

  4. #4
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,407
    Mentioned
    81 Post(s)
    Tagged
    3 Thread(s)
    If you have a local setup you can use I would recommend installing MySQL workbench as it is easier to see the results and any errors.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    joins are not required nor advised in this problem
    Code:
    SELECT DATE(`Date`) AS `Date`
         , COUNT(*) AS tttl 
         , COUNT(CASE WHEN fb = 'y' THEN fb ELSE NULL END) AS 'y'
         , COUNT(CASE WHEN fb = 'n' THEN fb ELSE NULL END) AS 'n'
         , COUNT(CASE WHEN fb = 'm' THEN fb ELSE NULL END) AS 'm'
      FROM fbdata  
     WHERE `Date` >= :wbegin 
       AND `Date`  < :wend + INTERVAL 1 DAY  
    GROUP 
        BY DATE(`Date`)
    note the WHERE condition for the datetimes -- this may optimize the performance if there's an index on the `Date` column (that's a poor name, by the way)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,100
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Rudy, you are brilliant.
    I bow to you. Your solution is so elegantly simple and worked right out of the box.
    On a side note, reserved keywords have gotten me many times, I can't believe that select date didn't throw an error.
    Page 285 of your book has DATE as a keyword.
    Thanks soo much.
    I have only written one query with CASE and didn't even (think of) consider it.
    So many thanks,
    Loren
    What I lack in acuracy I make up for in misteaks


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
  •