current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

How can i query and return all the rows but with "SUM" added to my query?

Here is my sample query: 

select SUM(amount) as total, user_id, username, from table where user_cat = '1';

if I remove SUM, I get the correct number of rows returned, but when I add SUM, the row result is decreased.

Any Idea how to do this?

share|improve this question
    
So you want the results of when you query with SUM and without SUM in one query? Your best off doing two separate queries or doing the math in the application layer (as opposed to the database query if you need to limit your number of queries for some reason) –  Skram Aug 2 '14 at 17:35
    
You can't use aggregate functions (like SUM) in your queries which return rows more than 1. You can make query and than count results with PHP. –  Eray Aug 2 '14 at 17:38
    
Do you want to count rows or do math? If you want to count, then use COUNT and not SUM –  Fred -ii- Aug 2 '14 at 17:41
1  
thank you guys for all the answers. –  juan.mannalon Aug 2 '14 at 17:43
    
You're welcome ;) –  Fred -ii- Aug 2 '14 at 17:45

1 Answer 1

up vote 0 down vote

You have noticed it's impossible to make the query in the way you tried as SUM() aggregates the other rows and only one row is displayed. One way to solve this is to separate SUM() in a subquery as described below:

select n.total, 
  user_id, 
  username  
  from table, 
  (select SUM(amount) as total 
    from table 
    where user_cat = '1') n 
  where user_cat = '1';

I prefer to make this in two separate queries. Now you'll have to deal with the first column and make it invisible. If you use two queries you'll have to deal with the second query. The choice is yours.

share|improve this answer
1  
Please improve your answer with some additional explanations –  abforce Aug 2 '14 at 18:34
    
@ABFORCE Of course, but there was a match on the tv and I didn't want to miss it... –  LHristov Aug 2 '14 at 19:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.