Mysql query to find count for different column

Question

I have a table : tbl_uesr

admission     discharge        name       unit

2017-05-07    2017-05-10       Deepu      100
2017-05-07    2017-05-09       Nisanth    100
2017-05-08    2017-05-10       Ragesh     100
2017-05-15    2017-05-20       Vidhu      100
2017-05-15    2017-05-21       Reshma     100

2017-05-15    2017-05-21       Sneha      104
2017-06-01    2017-06-10       Lekshmi    104
2017-06-03    2017-06-10       Nidhin     104
2017-06-17    2017-06-20       Vineesh    104

2017-05-07    2017-05-08       Rithun     107

The above table describes a particular user's admission date, discharge date and its unit. From the above table I have to find, for a given date and unit how many admission happened, how many discharge happened.

For example on 2017-05-07, there is 2 admission in unit 100, 1 admission in unit 107, no discharge in any unit.

So in my result I want all dates(admission date and discharge date), number of admission, number of discharge

Expected result

Date           no_admission     no_discharge     unit

2017-05-07     2                0                100
2017-05-07     1                0                107
2017-05-08     1                0                100
2017-05-08     0                1                107
2017-05-15     2                0                100 
2017-05-15     1                0                104 
2017-05-09     0                1                100
2017-05-10     0                3                100
2017-05-20     0                1                100
2017-05-21     0                1                100
2017-05-21     0                1                104
2017-06-01     1                0                104
2017-06-03     1                0                104
2017-06-17     1                0                104
2017-06-10     0                2                104
2017-06-20     0                1                104

What I have tried:

I have no idea to take all dates to a new table(result) as above. I tried to find no_admission by:

SELECT admission as date, count(name) as no_admission, unit FROMtbl_user GROUP BY admission

And tried to find the no_discharge by:

SELECT discharge as date, count(name) as no_discharge, unit FROMtbl_user GROUP BY discharge

But still am unable to find my result as expected..

Please help me to find the expected result..

I personally would just run two queries to get the count of each grouped by date and add those to a single array indexed by the date. IMO, any other options would quickly become easily confusing/unreadable and harder to maintain. — Jonathan Kuhn
– Jonathan Kuhn, Commented Jun 20, 2017 at 18:21

xQbert · Accepted Answer · 2017-06-20 18:26:38Z

2

The tricky part here is that you have two dates on the same line that sometimes need to be counted; sometimes not and really need to be on two different lines for each date. One way to achieve this is to unpivot the dates using two select statements and then sum.

SELECT d.mdate, sum(d.no_Admission) as No_Admission, sum(d.no_Discharge) as no_Discharge, d.unit
FROM (SELECT admission as mdate, 1 as No_Admission, 0 as No_Discharge, unit
      FROM tbl_user
      UNION ALL

      SELECT discharge as mdate, 0 as No_Admission, 1 as No_Discharge, unit
      FROM tbl_user) d
GROUP BY d.mdate, d.unit

Another way may be to create a distinct list of dates then left join in the appropriate records based on date. The below is untested and I'm unsure if the records would have the counts artificially inflated due to the multiple joins to tlb_user. I think not; but not positive.

SELECT mdate
     , count(A.Admission) as No_Admission
     , count(D.Discharge) as No_Discharge
     , coalesce(A.Unit, D.Unit) as unit
FROM (SELECT admission as mdate FROM tbl_user
      UNION 
      SELECT discharge as mdate FROM tbl_user) d
LEFT JOIN tbl_user A
 on d.mDate = A.admission
LEFT JOIN tbl_user D
 on d.mdate = D.Discharge
 and A.Unit = D.Unit
GROUP BY mdate, coalsece(A.Unit, D.Unit)

edited Jun 20, 2017 at 18:26

answered Jun 20, 2017 at 18:16

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Deepu Sasidharan Over a year ago

First query you provided is working..Second query gives` FUNCTION db_name.coalsece does not exist` exception

Collectives™ on Stack Overflow

Mysql query to find count for different column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related