Sum Query Based on Criteria

FullMoonMadness · December 16, 2021, 5:34am

Hello,

I would like to sum each field where the record’s “Payment Card” field doesn’t contain particular text.

The “Payment Card” field might contain “cash,” “credit card,” or debit card," so I’d like to sum fields not containing “card.”

I’ve tried using NOT LIKE and similar, but can’t seem to figure out the syntax. Here is a working sum of each field, I just cant figure out where to specify the part of not summing records with “Payment Card” containing the word “card”:

SELECT YEAR( “Purchase Date” ) AS “Year”, SUM( “Face Value” ) AS “Total Face Value”, SUM( “Charge” ) AS “Total Charged”, SUM( “Charge” - ( IFNULL( “Extra GC”, 0 ) + IFNULL( “CB Earned”, 0 ) + IFNULL( “Offer Earned”, 0 ) ) ) AS “Total Cost” FROM “Transactions” GROUP BY YEAR( “Purchase Date” )

Thanks!

Villeroy · December 16, 2021, 5:53am

SELECT YEAR( “Purchase Date” ) AS “Year”, SUM( “Face Value” ) AS “Total Face Value”, SUM( “Charge” ) AS “Total Charged”, SUM( “Charge” - ( IFNULL( “Extra GC”, 0 ) + IFNULL( “CB Earned”, 0 ) + IFNULL( “Offer Earned”, 0 ) ) ) AS “Total Cost” 
FROM “Transactions” 
WHERE LOCATE('card', LOWER("Payment Card")) = 0
GROUP BY YEAR( “Purchase Date” )

assuming that you are working with embedded HSQL documented here: Chapter 9. SQL Syntax

FullMoonMadness · December 17, 2021, 4:47am

Hi Villeroy,

Thank you for the response! I tried your suggestion, but it didn’t work. However, I looked at the link you provided, reviewed the syntax there, added the following, and I believe this did the job:

WHERE “Payment Card” NOT LIKE ‘%card’

I’m keeping that link as it may be of help in the future. Thanks for taking the time to assist!

Villeroy · December 17, 2021, 4:49pm

I tried it too and it works form me. I shows all records where card, Card, CARD etc. does not occur because the lower cased field does not match the lower cased word ‘card’ anywhere.
Your solution is perfectly valid. It matches card, Card, CARD etc. at the end of the field. I was not so sure if pattern matching is case-sensitive or not. It is not.