How to display two counts using oracle databases?-CodePudding

I have a list of people and I have to display the count of both male and female entries.

SELECT COUNT(gender)
  FROM people a
  JOIN accounts b
    ON b.idpers = a.idpers
  JOIN cards c
    ON c.nrc = b.nrc
 WHERE c.category = 'CREDIT'
   AND a.gender = :gender

I have tried this and it works, but I want it to display the number of male and female at the same time without the prompt input. Any ideas?

CodePudding user response：

You could do this by group by gender. Here is how you could do this

SELECT gender,COUNT(gender)
FROM people a
JOIN accounts b ON b.idpers=a.idpers
JOIN cards c ON c.nrc=b.nrc
WHERE c.category= 'CREDIT' group by gender;

CodePudding user response：

You could group the query by the gender:

SELECT   gender, COUNT(*)
FROM     people a
JOIN     accounts b ON b.idpers = a.idpers
JOIN     cards c ON c.nrc = b.nrc
WHERE    c.category= 'CREDIT'
GROUP BY gender

CodePudding user response：

You can use conditional aggregation such as

SELECT SUM(CASE WHEN gender = 'male' THEN 1 END) AS count_male,
       SUM(CASE WHEN gender = 'female' THEN 1 END) AS count_female  
  FROM people p
  JOIN accounts a
    ON a.idpers = p.idpers
  JOIN cards c
    ON c.nrc = a.nrc
 WHERE c.category = 'CREDIT'

which displays both counts on the same row