Sql combining 2 queries with different order by group
I have a query where I am calculating the most frequent response in the database and evaluating them by the maximum amount, so I am using the group by and order by.
Below is 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 a different column in the same table a different query similar to the following, so that two queries using the same sql statement, for example:
Health Count Diet Count
----------- ----- ----- -----
Very Good 6 Very Good 6
Good 5 Good 4
Poor 4 Poor 3
UPDATE !!
Hi, this is what 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 a different column in the same table a different query similar to the following, so that two queries using the same sql statement, for example:
Health Count Diet Count
----------- ----- ----- -----
Very Good 2 Very Good 1
Poor 1 Good 1
Good 0 Poor 1
Can anyone help me with this? If necessary, he can provide additional clarifications!
source to share
Here are two different ways to do it, note that 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')
Request 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 up the results is like it's really not a good practice, so here's another solution
Request 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
source to share
You need to join the table to yourself, but (as your data samples show) to close the gaps in the actual data for certain values.
If you have a table 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 a table like this, you need to generate the list of values manually and join this:
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 generate zeros if no matching data is found for the value.
Note that there is a redundant column in your desired output - you are repeating a column of values. The above queries produce output that looks like this:
Status HealthCount DietCount
-------------------------------
Very Good 2 1
Good 1 1
Poor 0 1
source to share