sql combining 2 queries with different order by group by

Question

I have a query where I am counting the most frequent response in a database and ranking them by highest amount so using group by and order by.

The following shows how to do it for one:

select health, count(health) as count 
from [Health].[Questionaire] 
group by Health 
order by count(Health) desc

which outputs the following:

Health       Count
-----------  -----
Very Good    6
Good         5
Poor         4

I would like to do with another column on the same table another query similar to the following so two queries using one sql statement like the following:

Health       Count    Diet       Count
-----------  -----   -----       -----
Very Good    6        Very Good   6
Good         5        Good        4
Poor         4        Poor        3

UPDATE!!

Hello this is how the table looks like at the moment

ID           Diet              Health      
-----------  -----             -------       
101          Very Good         Very Good   
102          Poor              Good        
103          Poor              Poor        

I would like to do with another column on the same table another query similar to the following so two queries using one sql statement like the following:

Health       Count    Diet       Count
-----------  -----   -----       -----
Very Good    2        Very Good   1
Poor         1        Good        1
Good         0        Poor        1

Can anyone please help me out with this one? Can provide further clarification if needed!

Answer 1

You need to join the table to itself, but (as your sample data shows) to deal with gaps in actual data for specific values.

If you have a table that has the range of health/diet values:

select
  v.value Status,
  count(a.id) healthCount,
  count(b.id) DietCount
from health_diet_values v
left join Questionaire a on a.health = v.value
left join Questionaire b on b.diet = v.value
group by v.value

or if you don't have such a table, you need to generate the list of values manually and join from that:

select
  v.value Status,
  count(a.id) healthCount,
  count(b.id) DietCount
from (select 'Very Good' value union all
      select 'Good' union all
      select 'Poor') v
left join Questionaire a on a.health = v.value
left join Questionaire b on b.diet = v.value
group by v.value

Both of these queries produce zeroes if there is no matching data for the value.

Note that in your desired output you have a redundant column - you repeat the value column. The above queries produce output that looks like:

Status    HealthCount DietCount
-------------------------------
Very Good           2         1
Good                1         1
Poor                0         1

Answer 2

Here are 2 different ways of doing it, notice i removed the redundant column:

Test data:

DECLARE @t table(Health varchar(20), Diet varchar(20))
INSERT @t values
('Very good', 'Very good'),
('Poor', 'Good'),
('Poor', 'Poor')

Query 1:

;WITH CTE1 as
(
  SELECT Health, count(*) CountHealth
  FROM @t --[Health].[Questionaire] 
  GROUP BY health
), CTE2 as
(
  SELECT Diet, count(*) CountDiet
  FROM @t --[Health].[Questionaire] 
  GROUP BY Diet
)
SELECT 
  coalesce(Health, Diet) Grade, 
  coalesce(CountHealth, 0) CountHealth, 
  coalesce(CountDiet, 0) CountDiet
FROM CTE1
FULL JOIN
CTE2
ON CTE1.Health = CTE2.Diet
ORDER BY CountHealth DESC

Result 1:

Grade     CountHealth  CountDiet
Poor      2            1
Very good 1            1
Good      0            1

Mixing the results like that is really not good practice, so here is a different solution

Query 2:

SELECT Health, count(*) Count, 'Health' Grade
FROM @t --[Health].[Questionaire] 
GROUP BY health
UNION ALL
SELECT Diet, count(*) CountDiet, 'Diet'
FROM @t --[Health].[Questionaire] 
GROUP BY Diet
ORDER BY Grade, Count DESC

Result 2:

Health     Count Grade
Good       1     Diet
Poor       1     Diet
Very good  1     Diet
Poor       2     Health
Very good  1     Health