如何根据约束确定列中值的计数？

I have a large dataset and I was wondering how to calculate the most common occurrences of a certain attribute's value.

我有一个大型数据集,我想知道如何计算某个属性值的最常见事件。

For instance, let's say I have a hypothetical table called Cats. I insert the following values into it:

例如,假设我有一个名为Cats的假设表。我在其中插入以下值:

Create table Cats (Cat_ID int, Cat_name varchar(max), Cat_Hometown varchar(max), Gender varchar(max), Birth_Year int)

INSERT INTO CATS (Cat_ID, Cat_Name, Cat_Hometown, Gender, Birth_year)
VALUES (1, 'Blue','Boston','M', 1980),
(2, 'Steamer','Plymouth','F', 1999),
(3, 'Stack','Newton','F', 1980),
(4, 'Overflow','Boston','M', 1978),
(5, 'CatorDog','Allston','F', 1999);

What if I want to determine the most common city for female cats in the year 1980? How would I do so? I can't seem to wrap my head around it.

如果我想确定1980年最常见的雌猫城市怎么办?我该怎么办?我似乎无法绕过它。

2 个解决方案

#1

You can try the below query to do so.

您可以尝试以下查询来执行此操作。

select Cat_Hometown, count(Cat_Hometown) as Cats_Count from Cats where gender='F' and Birth_year=1980 group by Cat_Hometown order by count(Cat_Hometown) desc limit 1;

选择Cat_Hometown,将Cats_Count计算为Cats_Count,其中gender ='F',Birth_year = 1980 group by Cat_Hometown order by count(Cat_Hometown)desc limit 1;

#2

create table #Cats (Cat_ID int, Cat_name varchar(max), cat_hometown varchar(max), Gender varchar(max), Birth_Year int)

INSERT Into #CATS (Cat_ID, Cat_Name, Cat_Hometown, Gender, Birth_year)
VALUES (1, 'Blue','Boston','M', 1980),
(2, 'Steamer','Plymouth','F', 1999),
(3, 'Stack','Newton','F', 1980),
(4, 'Overflow','Boston','M', 1978),
(5, 'CatorDog','Allston','F', 1999);

;with cat_count as (
select count(1) NumberofCats, Cat_Hometown from #Cats
where Gender = 'F' 
and Birth_year = 1980
group by cat_hometown
)

select Cat_Hometown from cat_count
where NumberofCats = (select max(numberofcats) from cat_count)

#1