SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help with query ...

    Hello:

    I am trying to get a mysql query together an not quite sure how to apply it, whether it is with distinct, join and all. What I am looking is to get the ID, qty ( number of users ), max and min number. Can anyone help me with this please.


    Name ID Name
    123ABC Engine
    649DFA Transmission
    854UDF Window


    User ID User Name User Amount
    123ABC Paul 300
    123ABC John 190
    649DFA Ian 500
    123ABC Mary 210
    123ABC Mark 225
    649DFA Steve 360




    Required Result:

    ID Qty Max Mix
    123ABC 4 300 190
    649DFA 2 500 360

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    could you please do a SHOW CREATE TABLE for each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Hypothetically, considering you haven't provided a schema:

    Code SQL:
    SELECT
         i.name_id id
         ,COUNT(u.user_id) qty
         ,MAX(u.user_amount) max_users
         ,MIN(u.user_amount) min_users
      FROM
         items i
      LEFT OUTER
      JOIN
         users u
        ON
         i.name_id = u.user_id
     GROUP
        BY
         i.name_id


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
  •