0

The documentation for the SQL SUM function lists its "signature" like this:

SUM([DISTINCT] expr)

The only examples listed for expr are single column names (like units, price, etc.), but can one do more with it? expr sounds very broad and open...

I'm especially interested in if it's possible to do any sort of selection of what exactly to sum. For example, I'd really like to do the following right now:

SELECT
    SUM(hours) 'Hours total',
    SUM(hours WHERE date BETWEEN '2017-10-01' AND '2017-10-31') 'Hours last month'

But that doesn't run... Probably too much of an expr dream, but maybe...? Maybe not?

Is anything like that possible with the SUM function? What can expr be?

Svish
  • 152,914
  • 173
  • 462
  • 620
  • 1
    `SUM(CASE WHEN date between '2017-10-01' AND '2017-10-31' THEN hours ELSE 0 END) as 'hours last month'` (that's an expression, not just a field). You could stick a subquery in there too. If it's legal in your SELECT portion of the query, then it's most likely legal inside your aggregate function. – JNevill Nov 15 '17 at 20:47
  • 1
    To add to what @JNevill said, you can omit the `ELSE 0` as aggregation functions like SUM (and COUNT, MIN, etc...) ignore NULL values (expect for that they return null if they ONLY processed null values). It is not so important in SUM, but can be very useful in COUNT and AVG; as COUNT counts 0's, and 0's would pull an average toward 0. – Uueerdo Nov 15 '17 at 20:59
  • 1
    A common trick is to conditionally count: `SUM(a>b)` counts how many rows have `a>b`. This works because `TRUE`==1 and `FALSE`==0. – Rick James Nov 16 '17 at 14:14

1 Answers1

0

Maybe you could use query like:

SELECT
SUM(hours) 'Hours total',
SUM( CASE WHEN date BETWEEN '2017-10-01' AND '2017-10-31' 
THEN hours 
ELSE 0 END) 'Hours last month'
from table
MRsa
  • 666
  • 4
  • 8