SQL – count function not working correctly

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

Answer

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;