CODEWITHSUNDEEP
sqldatabaseoracle-databasecount
Arwa
Arwa

Reputation: 166

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

enter image description here

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

Upvotes: 0

Views: 271

Answers (2)

Marc
Marc

Reputation: 11613

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

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

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;

Upvotes: 1

Related Questions