Alternative to CASE Statement to Compare and Summarize Data-CodePudding

I have a database that looks like the following;

--------------------------------
region | price_a | price_b
--------------------------------
USA    |  100    |  120
USA    |  150    |  150
Canada |  300    |  300
Mexico |   20    |  25

I need to compare the values from each price column and count the matched and mismatched prices and summarize as follows:

Required Results

--------------------------------
region | price_match | price_mismatch
--------------------------------
USA    |    1        |    1
Canada |    1        |    0
Mexico |    0        |    1

I can do this via multiple case statements (below) but I'm wondering if there is a better approach.

Current Code:

SELECT
    region,
    COUNT(CASE WHEN price_a = price_b THEN 'match' END) AS price_match,
    COUNT(CASE WHEN price_a != price_b THEN 'match' END) AS price_mismatch
FROM
    FOO
GROUP BY region;

CodePudding user response：

I am guessing from your recent questions, you're using Snowflake, in which case you can use a more compact syntax. I still think using case expression is better from a documentation and portability standpoint;

select region, 
       sum(iff(price_a=price_b,1,0)) price_match,
       sum(iff(price_a=price_b,0,1)) price_mismatch
from cte
group by region;

CodePudding user response：

You can use sum:

select region, sum(price_a = price_b), sum(price_a != price_b) 
from foo 
group by region