How to create sql selection based on condition?

Question

I have the following database which shows characteristics of attributes as follows:

attributeId | attributeCode | groupCode
------------+---------------+-----------
1           | 10            | 50
1           | 10            | 50
1           | 12            | 50

My desired result from a select would be:

attributeId | groupcount | code10 | code12
------------+------------+--------+--------
1           | 1          | 2      | 1

Which means: attributeId = 1 has only one groupCode (50), where attributeCode=10 occurs 2 times and attributeCode=12 occurs 1 time.

Of course the following is not valid, but you get the idea of what I'm trying to achieve:

select attributeId,
       count(distinct(groupCode)) as groupcount,
       attributeCode = 10 as code10, 
       attributeCode = 12 as code12
from table
group by attributeId;

Giorgos Betsos · Accepted Answer · 2016-03-02 10:37:54Z

2

Try this:

SELECT attributeId, COUNT(DISTINCT groupCode) AS groupcount,
       COUNT(CASE WHEN attributeCode = 10 THEN 1 END) AS code10,
       COUNT(CASE WHEN attributeCode = 12 THEN 1 END) AS code12
FROM mytable
GROUP BY attributeId

Demo here

edited Mar 2, 2016 at 10:37

answered Mar 2, 2016 at 10:36

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

membersound Over a year ago

That's interesting. Do you think it matters if I use COUNT or SUM(..) (both work and produce the same results)?

Giorgos Betsos Over a year ago

@membersound It should return the same result. I personally find COUNT more suitable because it expresses what you really want to do in this case: count the number of either 10 or 12 value occurrences.

Collectives™ on Stack Overflow

How to create sql selection based on condition?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related