CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jonah
Jonah

Reputation: 16222

Using boolean logic inside SUM function

In SQL Server 2008, this query works:

SELECT 
    SUM(CAST(isredeemed AS TINYINT)) AS totalredeemed,
FROM rewards
GROUP BY merchantid

It gives you the number of redeemed rewards by merchant. The TINYINT cast is need to avoid the error Operand data type bit is invalid for sum operator.

Now I'd like to do a similar query, but one that only finds rewards redeemed in the last few days. I tried this:

SELECT 
    SUM(CAST((isredeemed & ( MIN(dateredeemed) > '2014-01-10 05:00:00')) AS TINYINT)) AS claimedthisweek,
FROM rewards
GROUP BY merchantid

and I get the error

Incorrect syntax near '>'.

I also tried replacing & with && and also with AND. But those don't work either.

How can I make the example work?

Upvotes: 0

Views: 915

Answers (4)

T I
T I

Reputation: 9933

This question lacks detail to give an exact answer but you need to use a derived table or subquery for the calculation.

something like this 

SELECT r1.merchantid, r2.claimedthisweek
FROM rewards r1
JOIN (
    SELECT merchantid, SUM(CAST(isredeemed AS INT)) claimedthisweek
    FROM rewards
    GROUP BY merchantid
    HAVING MIN(dateredeemed) > '20140101'
)  r2

Upvotes: 1

Nix
Nix

Reputation: 58522

I think you want this would work but it would count some twice:

SELECT 
    SUM(case when MIN(dateredeemed) > '2014-01-10 05:00:00' then 1 else 0 end )    
FROM rewards
GROUP BY merchantid

Upvotes: 0

jamrolls
jamrolls

Reputation: 1

Any reason not to do the filtering at the "where" clause level? That should work so long as all rows you're aggregating match the same criteria:

SELECT 
    SUM(CAST(isredeemed AS TINYINT)) AS claimedthisweek
FROM rewards
WHERE dateredeemed > '2014-01-10 05:00:00'
GROUP BY merchantid

Upvotes: 0

Francisco Reis
Francisco Reis

Reputation: 11

This should be on the having clause, like this:

  SELECT 
  SUM(CAST((isredeemed AS TINYINT)) AS claimedthisweek
  FROM rewards
  GROUP BY merchantid
  HAVING MIN(dateredeemed) > '2014-01-10 05:00:00'

Upvotes: 0

Related Questions