1. ## 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

2. SELECT id, SUM(amount) FROM table_1 WHERE userid = 2
GROUP BY id HAVING SUM(amount) = 270

3. Unfortunatelly I get 0 rows found with that query.

4. 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. 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

6. 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. 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.

#### Posting Permissions

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