CODEWITHSUNDEEP
mysqlsql
MickeyThreeSheds
MickeyThreeSheds

Reputation: 1016

Returning totals of a field - grouped by a condition?

Let's say I have the following SQL statement...

SELECT amount FROM exampleTable

Pretty simple, that gives me every row, (just the amount field) from the example table... but I kind of want something a little different...

Instead of getting back all the rows, I would essentially like two rows. What I want is the total on the sum of "amount" where the value was positive, and also the sum of where it was negative. I would also like to return the count for each.

Is that doable? If so, can someone show me an example of how I might do that?

Before the "google it" answers come in... I am very new to SQL - and I have been looking at GROUP BY, sum, etc - and I am fairly confident that I need those - but to be honest, I haven't made it actually work - If someone can show me how I might get this working - then I might actually learn something!

Upvotes: 0

Views: 30

Answers (1)

Daniel Marcus
Daniel Marcus

Reputation: 2686

Use conditional aggregation 

SELECT sum(case when amount < 0 then amount else 0 end) negtotal,  
       sum(case when amount > 0 then amount else 0 end) postotal,
       sum(case when amount < 0 then 1 else 0 end) negcount,  
       sum(case when amount > 0 then 1 else 0 end) poscount
FROM exampleTable;

Upvotes: 4

Related Questions