Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Aug 2011
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Shows the amount of leads for each day--- help

    I need some help please. I am trying to show how many lead CIQFY sold on any given date but my MySQL skill are still lacking.

    Here is my code:

    PHP Code:
    $query_str1 "SELECT date_day, afid_seller_name, COUNT(afid_seller_name) "
                          
    " FROM lead_partners_pages " 
                          
    " WHERE afid_seller_name = 'CIQFY' AND MONTH(date_day)=MONTH(NOW()) "
                          
    " GROUP BY date_day "
    +------------+--------------+-------------+
    | date_day | afid_seller_name| total_price |
    +------------+--------------+-------------+
    | 2011-12-22 | CIQFY | 1.50 |
    | 2011-12-22 | CIQFY | 1.50 |
    | 2011-12-21 | CIQFY | 1.50 |
    | 2011-12-21 | HKFER | 2.00 |
    | 2011-12-20 | CIQFY | 2.00 |
    | 2011-12-20 | HKFER | 3.00 |
    | 2011-12-20 | CIQFY | 3.00 |
    | 2011-12-20 | CIQFY | 3.00 |
    +------------+--------------+-------------+

    Here is what I want the result to look like. I know how to use php to format it.

    22. 2
    21. 1
    20. 3
    Last edited by Atrhick; 12-22-2011 at 04:45 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,198
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    There is no way the query you showed there is giving you the results you showed.

    For starters, the query you showed does WHERE afid_seller_name = 'CIQFY'
    but the results you show include some for seller 'HKFER'.

    *ASSUMING* that what you were really showing us was the raw data in the table, then you would want this:

    Code:
    SELECT afid_seller_name, date_day, SUM(total_price) AS dailyTotal
    FROM ead_partners_pages
    GROUP BY afid_seller_name, date_day
    ORDER BY afid_seller_name, date_day
    *OR*
    Code:
    SELECT afid_seller_name, date_day, SUM(total_price) AS dailyTotal
    FROM ead_partners_pages
    WHERE afid_seller_name = 'CIQFY'
    GROUP BY afid_seller_name, date_day
    ORDER BY afid_seller_name, date_day
    *OR*
    Code:
    SELECT date_day, SUM(total_price) AS dailyTotal
    FROM ead_partners_pages
    WHERE afid_seller_name = 'CIQFY'
    GROUP BY date_day
    ORDER BY date_day
    [/code]
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Aug 2011
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    There is no way the query you showed there is giving you the results you showed.

    For starters, the query you showed does WHERE afid_seller_name = 'CIQFY'
    but the results you show include some for seller 'HKFER'.

    *ASSUMING* that what you were really showing us was the raw data in the table, then you would want this:

    Code:
    SELECT afid_seller_name, date_day, SUM(total_price) AS dailyTotal
    FROM ead_partners_pages
    GROUP BY afid_seller_name, date_day
    ORDER BY afid_seller_name, date_day
    *OR*
    Code:
    SELECT afid_seller_name, date_day, SUM(total_price) AS dailyTotal
    FROM ead_partners_pages
    WHERE afid_seller_name = 'CIQFY'
    GROUP BY afid_seller_name, date_day
    ORDER BY afid_seller_name, date_day
    *OR*
    Code:
    SELECT date_day, SUM(total_price) AS dailyTotal
    FROM ead_partners_pages
    WHERE afid_seller_name = 'CIQFY'
    GROUP BY date_day
    ORDER BY date_day
    you are right i was showing my table structure. however i dont want to show the sum of the price i want to total times CIQFY show up in the table for any given day.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,198
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    Fine, so just change SUM(total_price) to COUNT(*)

    But now we are back to exactly the query you showed in your first post. So I don't understand why it didn't work.

    Did you try it with a DB tool, *NOT* using your own PHP code?

    OH WAIT! I see. You want to *only* show the day of the month.

    So do that in PHP! Just use
    Code:
    date_format($row["date_day"], "d")
    to show only the day.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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