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,275
  • 14
  • 50
  • 62
  • 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
  • StackOverflow is not a free coding service. SO expects you to [try to solve your own problem first](http://meta.stackoverflow.com/questions/261592). Please update your question to show what you have already tried in a [mcve]. For further information, please see [ask], and take the [tour] :) – 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
  • 42,799
  • 6
  • 22
  • 44
0

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

Brayden
  • 201
  • 1
  • 7