SQL - count function not working correctly

Question

I'm trying to count the blood type for each blood bank I'm using oracle DB the blood bank table is created like this

    CREATE TABLE BloodBank (
  BB_ID number(15),
  BB_name varchar2(255) not NULL,
  B_type varchar2(255),CONSTRAINT
  blood_ty_pk FOREIGN KEY
  (B_type) references BloodType(B_type),
  salary number(15) not Null,
  PRIMARY KEY (BB_ID)
);
INSERT INTO BloodBank (BB_ID,BB_name,b_type, salary) 
VALUES (370,'new york Blood Bank','A+,A-,B+',12000);
INSERT INTO BloodBank (BB_ID,BB_name,b_type, salary) 
VALUES (791,'chicago Blood Bank','B+,AB-,O-',90000);
INSERT INTO BloodBank (BB_ID,BB_name,b_type, salary) 
VALUES (246,'los angeles Blood Bank','O+,A-,AB+',4500);
INSERT INTO BloodBank (BB_ID,BB_name,b_type, salary) 
VALUES (360,'boston Blood Bank','A+,AB+',13000);
INSERT INTO BloodBank (BB_ID,BB_name,b_type, salary) 
VALUES (510,'seattle Blood Bank','AB+,AB-,B+',2300);
select * from BloodBank;

when I use the count function

select count(B_type)
from bloodbank
group by BB_ID;

the result would be like this

so why the count function is not working correctly? I'm trying to display each blood bank blood type count which is not only one in this case

Tip of the day: Don't store the blood types for each blood bank as a CSV string. Instead, create a separate record for each blood type. — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 22, 2020 at 4:26
What do you expect the output to look like? If you're grouping by an ID, which is unique, you will have exactly one of each in your count. — Marc
– Marc, Commented Nov 22, 2020 at 4:29

Marc · Accepted Answer · 2020-11-22 04:36:25Z

2

I hope I don't get downvoted for solving the specific problem you're asking about, but this query would work:

select bb_id,
       bb_name,
       REGEXP_COUNT(b_type, ',')+1
from bloodbank;

However, this solution ignores a MAJOR issue with your data, which is that you do not normalize it as @Tim Biegeleisen correctly instructs you to do. The solution I've provided is EXTREMELY hacky in that it counts the commas in your string to determine the number of blood types. This is not at all reliable, and you should 100% do what Tim B recommends. But for the circumstances you find yourself in, this will tell you how many different blood types are kept at a specific blood bank.

http://sqlfiddle.com/#!4/8ed1c2/2

answered Nov 22, 2020 at 4:36

Marc

11.6k2 gold badges40 silver badges48 bronze badges

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

Comments

Tim Biegeleisen · Accepted Answer · 2020-11-22 04:29:44Z

1

You should normalize your data and get each blood type value onto a separate record. That is, your starting data should look like this:

BB_ID | BB_name             | b_type | salary
370   | new york Blood Bank | A+     | 12000
370   | new york Blood Bank | A-     | 12000
370   | new york Blood Bank | A+     | 12000
... and so on

With this data model, the query you want is something along these lines:

SELECT BB_ID, BB_name, b_type, COUNT(*) AS cnt
FROM bloodbank
GROUP BY BB_ID, BB_name, b_type;

Or, if you want just counts of types across all bloodbanks, then use:

SELECT b_type, COUNT(*) AS cnt
FROM bloodbank
GROUP BY b_type;

answered Nov 22, 2020 at 4:29

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

1 Comment

Arwa Over a year ago

thank you so much I totally forgot about the normalization

Collectives™ on Stack Overflow

SQL - count function not working correctly

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related