I'd like to check the number of people born in 1991 and 1981.

Please find out how many celebrities were born in 1991 and 1981.However, do not use the date function, but use the UNION clause.

SELECT 
COUNT(birth),
SUBSTRING (birth, 1, 4) AS 'Year of Birth'
from 
`celebrities' 
WHERE birth > = '1991/1/1' AND birth < = '1991/12/31'
union
SELECT 
COUNT(birth),
SUBSTRING (birth, 1, 4) AS 'Year of Birth'
from 
`celebrities' 
WHERE birth>='1981/1/1'AND birth<='1981/12/31'

I was able to figure out the number of years of birth through SUBSTRING.
When I think that I just change the number with the count function, I get this kind of error.
If anyone understands, please let me know

#1140-Inaggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'world.celebrities.birth'; this is incompatible with sql_mode=only_full_group_by

mysql sql

2022-09-30 20:22

1 Answers

Aggregate functions such as count have features that are difficult for beginners to understand, and group by is required to select columns that are not aggregated.
However, since this question requires the use of UNION, it may be intended to answer the following question by rewriting the link to the comment.

SELECT '1991 'AS' Birthday', COUNT(id)
FROM Celebrities
WHERE SUBSTRING (birth, 1, 4) = '1991'
union
SELECT '1981', COUNT(id)
FROM Celebrities
WHERE SUBSTRING (birth, 1, 4) = '1981'

As you asked, the following SQL can use group by to derive similar results as an honest way to get a birth year with substring.
(I'm not being honest when I'm using the phrase with which I probably didn't learn.)but)

with bys
as(select id, substring(birth,1,4) 'year of birth'
     from celebrities)
the year of the year of birth,
       count(1)
from bys
where year of birth = 1991
group by year of birth
union all
the year of the year of birth,
       count(1)
from bys
where year of birth = 1981
group by year of birth
order by year of birth

2022-09-30 20:22

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656