SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Where Sum()

  1. #1
    SitePoint Enthusiast XRay's Avatar
    Join Date
    Aug 2006
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Where Sum()

    Hi Everyone, I need to create query which will select rows based on SUM().
    example:
    we have colimns:
    id amount userid date
    1 20 2 2007-12-12
    2 20 2 2007-12-15
    3 250 2 2007-12-10
    4 67 15 2007-12-13

    now I have let say 270(amount) and I would need to get which rows for user with id 2 are giving 270. Something like:
    SELECT id, amount FROM table_1 WHERE userid = 2 and SUM(amount) = 270

    Thanks in advanced

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    SELECT id, SUM(amount) FROM table_1 WHERE userid = 2
    GROUP BY id HAVING SUM(amount) = 270
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast XRay's Avatar
    Join Date
    Aug 2006
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Unfortunatelly I get 0 rows found with that query.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you want to find which, if any, combination of numbers is equal to 270 for a particular user, right? this is not possible purely in sql.

    may i ask why you need this information?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    that means there's no user whose amounts add up to exactly 270

    uh oh, i have a feeling that's not what you wanted

    how about HAVING SUM(amount) >= 270
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast XRay's Avatar
    Join Date
    Aug 2006
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    story is next: user can see his commissions in one select field where he can choose how much money I can send him. here is simple table with similar details:
    Code:
    structure for table `sum`
    -- 
    
    DROP TABLE IF EXISTS `sum`;
    CREATE TABLE `sum` (
      `ID` int(11) NOT NULL auto_increment,
      `Amount` float NOT NULL default '0',
      `SiteID` int(11) NOT NULL default '0',
      `UserID` int(11) NOT NULL default '0',
      PRIMARY KEY  (`ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
    
    -- 
    -- Dumping data for table `sum`
    -- 
    
    INSERT INTO `sum` (`ID`, `Amount`, `SiteID`, `UserID`) VALUES (1, 20, 4, 1),
    (2, 20, 4, 1),
    (3, 250, 4, 1),
    (4, 15, 2, 2);

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    that's what i figured. this is not easy (if even possible) to do in SQL.

    a better approach might be to display all available commission amounts in a list with check boxes. as the user checks the boxes, use javascript to update a running total on screen.

    if you really want to do the select box thing, you'll need to full all the commission amounts along with primary id in to an array and use your host language to calculate all the possible permutations.


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
  •