Oracle SQL: Count one value twice in a single column with different content

Question

Hi everyone I have the next Table

Name | Gender | Count(Gender)
BBC  |   M    |      31
BBC  |   F    |       1
BBC  |   B    |       3
BBC  |   N    |     160

M: Male
F: Female
B: Both
N: Not Specified

I need to group this in only three categories. M, F, N.

How can I make a Case Statement that when the register is B the Count for Male and Female increments in 1 for both.

I need a table like this.

Name | Gender | Count(Gender)
BBC  |   M    |      34
BBC  |   F    |       4
BBC  |   N    |     160

I hope I explained myself well.

Thanks to everyone.

That first table of data you have presented - is that the raw data or is it the result of an aggregation? — Christian Palmer
– Christian Palmer, Commented Mar 21, 2018 at 8:18
This comment would seem to indicate that it is the result of aggregation: How can I make a Case Statement that when the register is B the Count for Male and Female increments in 1 for both. — David Faber
– David Faber, Commented Mar 21, 2018 at 12:34

user5683823 · Accepted Answer · 2018-03-21 03:17:17Z

Here's one way to do this. Not the simplest code, perhaps, but I believe it is quite efficient. It uses an incomplete cross join to duplicate the 'B' rows - so that the base data is read just once.

with
  inputs ( name, gender, cnt ) as (
    select 'BBC', 'M',  31 from dual union all
    select 'BBC', 'F',   1 from dual union all
    select 'BBC', 'B',   3 from dual union all
    select 'BBC', 'N', 160 from dual union all    
    select 'ZYX', 'M',  55 from dual union all
    select 'ZYX', 'F',  12 from dual union all
    select 'ZYX', 'B',  43 from dual union all
    select 'ZYX', 'N', 123 from dual
)
select   i.name
     ,   case i.gender when 'B' then case h.flag when 1 then 'F' 
                                                 else        'M'
                                     end
                       else          i.gender
         end as gender
     ,   sum(cnt) as cnt
from     inputs i cross join
         ( select 1 as flag from dual union all select 2 from dual ) h
where    h.flag = 1 or i.gender = 'B'
group by i.name
       , case i.gender when 'B' then case h.flag when 1 then 'F' 
                                                 else        'M'
                                     end
                       else          i.gender
         end
order by name, gender
;

Output (from the extended test data I created in the WITH clause):

NAME  GENDER  CNT
----  ------  ---
BBC   F         4
BBC   M        34
BBC   N       160
ZYX   F        55
ZYX   M        98
ZYX   N       123

Christian Palmer · Accepted Answer · 2018-03-21 08:25:00Z

1

I think the other answers overcomplicate things. I am taking your original statement literally - you have a table of counts by gender. If this assumption does not hold the answer will change slightly. Just use a case statement to include B in both male and female counts, then aggregate :

WITH yourTable AS (
    SELECT 'BBC' AS Name, 'M' AS Gender, 31 AS cnt from dual UNION ALL
    SELECT 'BBC', 'F', 1  from dual UNION ALL
    SELECT 'BBC', 'B', 3  from dual UNION ALL
    SELECT 'BBC', 'N', 160 from dual
)
SELECT
 SUM(male)
,SUM(female)
,SUM(not_known)
FROM
 (SELECT
   gender
  ,cnt
  ,CASE
    WHEN gender IN ('M','B') THEN cnt
    ELSE 0
   END                          male
  ,CASE
    WHEN gender IN ('F','B') THEN cnt
    ELSE 0
   END                          female
  ,CASE
    WHEN gender = 'N' THEN cnt
    ELSE 0
   END                          not_known
  FROM
   yourTable
 )
;

answered Mar 21, 2018 at 8:25

Christian Palmer

1,3128 silver badges10 bronze badges

4 Comments

user5683823 Over a year ago

The OP shows the required output in a single "count" column. You show the result lined up in three columns. If the OP needs the output as input for further processing, he will need to add an unpivot of some sort... and if you add that, the solution doesn't look that much simpler anymore. Your answer is something kind of like this: "What's six times three?" Your answer: "Easy, six plus three is nine." Easy, but that wasn't the question, was it?

Christian Palmer Over a year ago

@mathguy OP : "I need to group this in only three categories. M, F, N." I've done that. Are you looking for compexity where there is none? The OP wanted to understand how to use a single value (B) to increment two other values (M and F). I see nothing in the question that asks for more

user5683823 Over a year ago

You don't see, in the original post, the part that begins with "I need a table like this." And then shows the counts lined up in a single column? I find that hard to believe. Perhaps you should look again?

Christian Palmer Over a year ago

@mathguy Hmmmm, okay, I can see you have a point of sorts. But the OP specifically asked about how to construct a CASE statement. I suspect the "I need a table like..." is really just imprecision, and means "I want data like..." but I guess it's up to the OP to decide what answer they prefer.

Tim Biegeleisen · Accepted Answer · 2018-03-21 01:51:12Z

0

I don't see a way of doing this without generating data for both stats to cover both male and female. We can take a union of a query which aggregates males and both along with one for females and both. The first half of the union also includes not specified, since it needs to come in from somewhere.

SELECT
    Name,
    CASE WHEN Gender IN ('M', 'B') THEN 'M' ELSE Gender END AS Gender,
    SUM(cnt) AS cnt
FROM yourTable
WHERE Gender IN ('M', 'B', 'N')
GROUP BY
    Name,
    CASE WHEN Gender IN ('M', 'B') THEN 'M' ELSE Gender END
UNION ALL
SELECT
    Name,
    CASE WHEN Gender IN ('F', 'B') THEN 'F' END AS Gender,
    SUM(cnt) AS cnt
FROM yourTable
WHERE Gender IN ('F', 'B')
GROUP BY
    Name,
    CASE WHEN Gender IN ('F', 'B') THEN 'F' END
ORDER BY
    Name, Gender

Demo

edited Mar 21, 2018 at 1:51

answered Mar 21, 2018 at 1:45

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

David Faber · Accepted Answer · 2018-03-21 12:37:07Z

I would do this by using conditional aggregation and then unpivoting:

SELECT * FROM (
    SELECT name, SUM(CASE WHEN gender IN ('M','B') THEN 1 ELSE 0 END) AS "M"
         , SUM(CASE WHEN gender IN ('F','B') THEN 1 ELSE 0 END) AS "F"
         , SUM(CASE WHEN gender = 'N' THEN 1 ELSE 0 END) AS "N"
      FROM my_table
     GROUP BY name
) UNPIVOT ( count_gender FOR gender IN ("M","F","N") );

The B values are counted as under both the M (male) and F (female) columns under the conditional aggregation - we can then unpivot to turn our columns into rows. This assuming you're using at least Oracle 11g - for Oracle 10g and below you'll have to use a query like the one given in Tim Biegeleisen's answer.

EDIT: If you have a table of counts (that is, if the table in your post is the raw data and not a result of aggregation), then substitute the count column above in the SUM()s in place of the "1":

SUM(CASE WHEN gender IN ('M','B') THEN count_gender ELSE 0 END) AS "M"

Hope this helps.

Ariza · Accepted Answer · 2018-03-21 08:55:10Z

-1

WITH yourTable AS (
    SELECT 'BBC' AS Name, 'M' AS Gender, 31 AS cnt from dual UNION ALL
    SELECT 'BBC', 'F', 1  from dual UNION ALL
    SELECT 'BBC', 'B', 3  from dual UNION ALL
    SELECT 'BBC', 'K', 3  from dual UNION ALL
    SELECT 'BBC', 'N', 160 from dual )select t.*,sum(cnt) over (partition by gender) from yourTable  t where gender in('F','M','N')

edited Mar 21, 2018 at 8:55

answered Mar 21, 2018 at 7:17

Ariza

12 bronze badges

2 Comments

rollstuhlfahrer Over a year ago

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value.

AndyDan Over a year ago

This seems to just ignore the "B" entries, not combine them with the "F" and "M" entries.

Collectives™ on Stack Overflow

Oracle SQL: Count one value twice in a single column with different content

5 Answers 5

Comments

4 Comments

Demo

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

4 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related