0

I have below data table and need to accomplish the result for Student count as per availability and per department

enter image description here Note: Different Department may come in later.

enter image description here

Want to have result as below: (Student count as per availability and per department) enter image description here

usr021986
  • 3,421
  • 14
  • 53
  • 64
  • Use a subquery to calculate the count per department and availability. Then combine those counts using `GROUP_CONCAT` in the main query. – Barmar Apr 20 '18 at 23:39
  • Do you really want `Yes, 2, No, 3` as a value in a column? I'd recommend just having 3 columns. – Brayden Apr 20 '18 at 23:40
  • Why does the first table have the department name instead of ID? – Barmar Apr 20 '18 at 23:40
  • 1
    Duplicate of https://stackoverflow.com/questions/33139000/multiple-query-same-table-but-in-different-columns-mysql/33139182#33139182 – Barmar Apr 20 '18 at 23:41

2 Answers2

1

First you need to get count by department,studavailablity on a subquery.then use GROUP_CONCAT function to create the studCount by subquery.

If you want to show Yes in front of No you could Add order by on GROUP_CONCAT function.

You can try this.

SELECT department,GROUP_CONCAT(Concat(studavailablity,' ',c) order by 1 DESC)  'count'
FROM(
  SELECT count(1) c,department,studavailablity 
  FROM T
  GROUP BY studavailablity,department
) t
group by department
order by department desc

sqlfiddle:http://sqlfiddle.com/#!9/c4e56f/16

D-Shih
  • 44,943
  • 6
  • 31
  • 51
0

How about SELECT Department, Studavailability, COUNT(*) FROM students GROUP BY Department, Studavailability;? Best of luck!

Brayden
  • 201
  • 1
  • 7