Sql-server – Group by Multiple columns and then count different value of same column

countgroup bysql serversum

I want to achieve the result which tells me the number of males and females of each disability types in each district.
each district can have multiple disabilities.

So far i have reached the following query :

  SELECT 
  DistrictId, 
  fb.DisabilityTypeId, 
  SUM(
    CASE WHEN GenderId = 1 THEN 1 ELSE 0 END
  ) AS Male, 
  SUM(
    CASE WHEN GenderId = 2 THEN 1 ELSE 0 END
  ) AS Female 
FROM 
  Districts d 
  LEFT OUTER JOIN FormAddresses a ON d.Id = a.DistrictId 
  INNER JOIN PeopleForms pf ON a.PeopleFormId = pf.Id 
  INNER JOIN FormBeneficiaries fb ON pf.Id = fb.PeopleFormId 
  INNER JOIN FormPersonalInfos fp ON pf.Id = fp.PeopleFormId 
where 
  a.IsDeleted = 0 
  AND pf.FormTypeId = 2 
  AND d.CityId = 3 
GROUP BY 
  DistrictId, 
  fp.GenderId,
  fb.DisabilityTypeId

which gives the following result :

DistrictId  |   DisabilityTypeId    |   Male  | Female
    1       |       2               |   1     |     0
    3       |       2               |   0     |     3
    5       |       16              |   1     |     0
    5       |       20              |   2     |     0
    5       |       20              |   0     |     1

But i want to achieve the following result :

DistrictId  |   DisabilityTypeId    |   Male  | Female
    1       |       2               |   1     |     3
    5       |       16              |   1     |     0
    5       |       20              |   2     |     1

i somehow managed to get the expected result, but that with some complex sub-queries in the select clause of each gender which i didnt like and was not sure about the performance.

how can i write an efficient query for the desired result that i want?

Thanks.

Best Answer

Remove gender from GROUP BY clause:

SELECT DistrictId
     , fb.DisabilityTypeId
     , SUM( CASE WHEN GenderId = 1 THEN 1 ELSE 0 END ) AS Male
     , SUM( CASE WHEN GenderId = 2 THEN 1 ELSE 0 END ) AS Female 
FROM Districts d 
LEFT JOIN FormAddresses a 
    ON d.Id = a.DistrictId 
JOIN PeopleForms pf 
    ON a.PeopleFormId = pf.Id 
JOIN FormBeneficiaries FB 
    ON pf.Id = fb.PeopleFormId 
JOIN FormPersonalInfos fp 
    ON pf.Id = fp.PeopleFormId 
WHERE a.IsDeleted = 0 
  AND pf.FormTypeId = 2 
  AND d.CityId = 3 
GROUP BY DistrictId
       , fb.DisabilityTypeId

Related Solutions

Mysql – Group by age range and gender with aggregation function on some columns

Two options.

a) Use sub query

select 
  concat(10*floor(age/10), '-', 10*floor(age/10) + 10) as `range`, 
  gender, 
  count(*) as count 
from (
  select 
    *, 
    TIMESTAMPDIFF(YEAR,birth_year,CURDATE()) AS age
  from 
    transaction left join patient on patient_id = patient.id
) as t group by `range`, gender;

Gives

+-------+--------+-------+
| range | gender | count |
+-------+--------+-------+
| 0-10  | m      |     2 |
| 10-20 | m      |     1 |
| 20-30 | m      |     3 |
| 30-40 | f      |     2 |
| 30-40 | m      |     2 |
| 50-60 | f      |     1 |
| 50-60 | m      |     1 |
| 60-70 | f      |     5 |
| 60-70 | m      |     3 |
| 70-80 | f      |     1 |
| 70-80 | m      |     6 |
+-------+--------+-------+

b) Group by age first and then group in range in application.

When using ORMs it's not easy to do sub queries, so this might be a good idea.

Performance wise: Considering that average humans live for 100 years, there can be at max 200 records (100 male and 100 female) which can be easily looped in an application.

select 
  gender, 
  TIMESTAMPDIFF(YEAR,birth_year,CURDATE()) AS age,
  count(*)
from 
  transaction left join patient on patient_id = patient.id
group by age, gender

Gives

+--------+------+----------+
| gender | age  | count(*) |
+--------+------+----------+
| m      |    4 |        2 |
| m      |   14 |        1 |
| m      |   24 |        3 |
| f      |   34 |        2 |
| m      |   34 |        2 |
| f      |   54 |        1 |
| m      |   54 |        1 |
| f      |   64 |        5 |
| m      |   64 |        3 |
| f      |   74 |        1 |
| m      |   74 |        6 |
+--------+------+----------+

Thesql: counting number of tickets which are open per day basis

You can use conditional sum for this calculation something as

select 
date_format(created,'%d-%m') as created_date , 
sum( case when status='open' then 1 else 0 end )+
sum( case when status='new' then 1 else 0 end )+
sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
from table_name
where
YEAR(Created) = '2015'
group by created_date ;

Here is a test case

mysql> select * from test ;
+------+---------------------+---------------------+----------+
| id   | created             | resolved            | status   |
+------+---------------------+---------------------+----------+
|    1 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | open     |
|    2 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | new      |
|    3 | 2015-05-10 00:00:00 | 2015-05-12 00:00:00 | resolved |
|    4 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | open     |
|    5 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | new      |
|    6 | 2015-05-11 00:00:00 | 2015-05-11 00:00:00 | resolved |
+------+---------------------+---------------------+----------+
6 rows in set (0.00 sec)

mysql> select 
    -> date_format(created,'%d-%m') as created_date , 
    -> sum( case when status='open' then 1 else 0 end )+
    -> sum( case when status='new' then 1 else 0 end )+
    -> sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
    -> from test
    -> where
    -> YEAR(Created) = '2015'
    -> group by created_date ;
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    2 |
+--------------+------+
2 rows in set (0.00 sec)

UPDATE The count of open is a incremental as per the comment. So the following should do the trick

select
created_date,
@open:= @open+open_ticket-resolved as open
from(
 select 
 date_format(created,'%d-%m') as created_date , 
 sum( case when status='open' then 1 else 0 end )+
 sum( case when status='new' then 1 else 0 end )+
 sum( case when status='resolved' and ( date(resolved) > date(created) or date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 sum(case when status = 'resolved' and date(resolved) = date(created) then 1 else 0 end) as resolved
 from test
 where
 YEAR(Created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

From the above sample data it will have

 +--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
+--------------+------+

UPDATE:

Count the previous tickets which got resolved on a given date and deduct that from the current day open ticket, for that you need a left join something as

select
created_date,
@open:= @open+open_ticket-resolved_ticket as open
from(
 select 
 date_format(t.created,'%d-%m') as created_date , 
 sum( case when t.status='open' then 1 else 0 end )+
 sum( case when t.status='new' then 1 else 0 end )+
 sum( case when t.status='resolved' and ( date(t.resolved) > date(created) or date(t.resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 coalesce(tot,0) as resolved_ticket
 from test t left join(
    select count(*) as tot, date(resolved) as resolved from test where status='resolved' group by date(resolved)
 )x on date(x.resolved) = date(t.created) 
 where
 YEAR(t.created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

OUTPUT :

+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
| 12-05        |    7 |
+--------------+------+

Best Answer

Related Solutions

Mysql – Group by age range and gender with aggregation function on some columns

Thesql: counting number of tickets which are open per day basis

Related Question