SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict
    Join Date
    Oct 2003
    Posts
    245
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Display more sales order by staff first?

    Code MySQL:
    SELECT * FROM sales WHERE submit_date >= 1277074800 AND submit_date <= 1277679600

    It output like this:

    3, Adam, Date_Submit
    4, Paul, Date_Submit
    5, Adam, Date_Submit
    6, John, Date_Submit
    7, Paul, Date_Submit
    8, Adam, Date_Submit

    I wanted to output which staff has the most sales come first, like this this:

    3, Adam, Date_Submit
    5, Adam, Date_Submit
    8, Adam, Date_Submit
    4, Paul, Date_Submit
    7, Paul, Date_Submit
    6, John, Date_Submit

    As you can Adam has 3, Paul has 2, and John has as 1. How to display in this way?
    [Home Sweet Home]

  2. #2
    Smart programmer silver trophy M.Zeb Khan's Avatar
    Join Date
    Jan 2004
    Location
    Luton, Beds
    Posts
    1,791
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you tell us the field names for number of sales and employee name.

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    There is no field for number of sales. To know the number of sales, the lines for each employee have to be counted.

  4. #4
    Smart programmer silver trophy M.Zeb Khan's Avatar
    Join Date
    Jan 2004
    Location
    Luton, Beds
    Posts
    1,791
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    at least tell us what is name of table field for emplyee?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT sales.id
         , sales.staffname
         , sales.submit_date
      FROM ( SELECT staffname
                  , COUNT(*) AS lines
               FROM sales
              WHERE submit_date >= 1277074800  
                AND submit_date <= 1277679600
             GROUP
                 BY staffname ) AS totals
    INNER
      JOIN sales
        ON sales.staffname = totals.staffname
     WHERE sales.submit_date >= 1277074800  
       AND sales.submit_date <= 1277679600
    ORDER
        BY totals.lines DESC
         , sales.submit_date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Oct 2003
    Posts
    245
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937.

    I have manged to this way:

    Code MySQL:
    SELECT * FROM sales f,
      (select username, count(*) as TotalUser from sales group by username) as f2  
      where f.username=f2.username  and 
      submit_date >= $StartMon AND submit_date <= $EndSun 
      order by TotalUser DESC";


    Isn't that better and shorter?
    [Home Sweet Home]

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You order by the users that have the most sales ever, not the most sales in the given period.

    And I'll leave it to Rudy to tell you about the INNER JOIN

  8. #8
    SitePoint Addict
    Join Date
    Oct 2003
    Posts
    245
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    You order by the users that have the most sales ever, not the most sales in the given period.

    And I'll leave it to Rudy to tell you about the INNER JOIN
    I did order by TotalUser... not user.
    [Home Sweet Home]

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You counted the sales each user made EVER, not only in the GIVEN PERIOD. That doesn't mean it's wrong, it all depends on what you want.


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
  •