Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Count male, female and total

Tags:

sql

sql-server

I want to count male, female and total students from Student table for a specific year specified. I wish if the result could be displayed in the form:

====================================
| Label    |  Value   |   Year     |
====================================
| Male     |   0      |   2013     |
| Female   |  23      |   2013     |
| Total    |  23      |   2013     |
====================================

The query should display 0 if there is no male/female matching for the specified year. Any idea how I can make this happen?

Thanks in advance

like image 778
aby Avatar asked Jun 15 '13 00:06

aby


People also ask

How to count Number of Male and Female in SQL?

Something like: select count(p. ps_gender) as Male, count(p1. ps_gender) as Female, p.

What is difference count * and count 1?

The simple answer is no – there is no difference at all. The COUNT(*) function counts the total rows in the table, including the NULL values. The semantics for COUNT(1) differ slightly; we'll discuss them later. However, the results for COUNT(*) and COUNT(1) are identical.

How do you change from male to female in SQL?

UPDATE [EMPDATA] SET GENDER = 'FEMALE'; This Query will Update GENDER = 'FEMALE' for all rows. Update statement with where clause: The Update Statement with the Where clause is used to Update a single or multiple rows on the basis of the WHERE clause in SQL Server.

How do I count the number of records in SQL?

Use the COUNT aggregate function to count the number of rows in a table. This function takes the name of the column as its argument (e.g., id ) and returns the number of rows for this particular column in the table (e.g., 5).


2 Answers

Consider the following query:

select
  max(registeredYear) as year,
  count(case when gender='Male' then 1 end) as male_cnt,
  count(case when gender='Female' then 1 end) as female_cnt,
  count(*) as total_cnt
from student
where registeredYear = 2013
group by registeredYear;

The result will be like this:

Year male_cnt female_cnt total_cnt
---- -------- ---------- ---------
2013        0         23        23

You can transform this result into the form you want. If you want to do it within a query, then you can do it like this:

with t as (
    select
      max(registeredYear) as year,
      count(case when gender='Male' then 1 end) as male_cnt,
      count(case when gender='Female' then 1 end) as female_cnt,
      count(*) as total_cnt
    from student
    where registeredYear = 2013
    group by registeredYear)
select 'Male', male_cnt as male, year from t
union all
select 'Female', female_cnt as male, year from t
union all
select 'Total', total_cnt as male, year from t
;
like image 152
ntalbs Avatar answered Oct 19 '22 23:10

ntalbs


You should use:

select name, COUNT(*)as tot, 
  COUNT(case when details.gender='male' then 1 end) as male,
  COUNT(case when details.gender='female' then 1 end) as female 
  from details  group by name
like image 26
Sudhagar VJ Avatar answered Oct 19 '22 23:10

Sudhagar VJ