snix123
asked on
SUM for SQL Query, Grouping and an OTHER column
Hello experts:
Suppose this is my data: "AnimalName" is a column name in my table called "myAnimals" and contains the following values:
I want to count the groups, but lump Giraffes and Elephants into a new group called OTHER. Also, I want to sum up everything. The output for the query should look like this:
ALL 19
Bird 4
Cat 5
Dog 4
Turtle 3
Other 3
I can do this:
I can't figure out how to get the OTHER.
Hope this makes sense! Thanks!
Suppose this is my data: "AnimalName" is a column name in my table called "myAnimals" and contains the following values:
Bird
Cat
Dog
Turtle
Turtle
Bird
Bird
Cat
Dog
Elephant
Cat
Cat
Dog
Bird
Cat
Turtle
Giraffe
Elephant
Dog
I want to count the groups, but lump Giraffes and Elephants into a new group called OTHER. Also, I want to sum up everything. The output for the query should look like this:
ALL 19
Bird 4
Cat 5
Dog 4
Turtle 3
Other 3
I can do this:
SELECT AnimalName, COUNT(*) as "COUNT" from myAnimals where AnimalName in ('Bird','Dog','Cat','Turtle') group by AnimalName
I can't figure out how to get the OTHER.
Hope this makes sense! Thanks!
Oh, I missed the COUNT(*) as the_Count as an additional field in select.
And to get the 'all':
select
case AnimalName
when 'Giraffes' then 'Other'
when 'Elephants' then 'Other'
else AnimalName
end as myAnimalName,
COUNT(1) AS Count
from myAnimals
group by case AnimalName
when 'Giraffes' then 'Other'
when 'Elephants' then 'Other'
else AnimalName
end
union all
SELECT 'ALL' as myAnimalName, COUNT(1) as Count
FROM myAnimals
ORDER BY 2 DESC, 1 ASC
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Careful -- if you add an "Aardvark", the previous code no longer puts "All" first.
CREATE TABLE #Animal (AnimalName VARCHAR(50));
INSERT INTO #Animal ( AnimalName )
VALUES
('Aardvark'),
('Bird'),
('Cat'),
('Dog'),
('Turtle'),
('Turtle'),
('Bird'),
('Bird'),
('Cat'),
('Dog'),
('Elephant'),
('Cat'),
('Cat'),
('Dog'),
('Bird'),
('Cat'),
('Turtle'),
('Giraffe'),
('Elephant'),
('Dog')
SELECT ISNULL(CASE A.AnimalName WHEN 'Elephant' THEN 'Other' WHEN 'Giraffe' THEN 'Other' ELSE A.AnimalName END, 'All') AnimalName, COUNT(*) Count
FROM #Animal A
GROUP BY CASE A.AnimalName WHEN 'Elephant' THEN 'Other' WHEN 'Giraffe' THEN 'Other' ELSE A.AnimalName END
WITH ROLLUP
ORDER BY GROUPING ( CASE A.AnimalName WHEN 'Elephant' THEN 'Other' WHEN 'Giraffe' THEN 'Other' ELSE A.AnimalName END ) DESC, AnimalName
DROP TABLE #Animal
CREATE TABLE #Animal (AnimalName VARCHAR(50));
INSERT INTO #Animal ( AnimalName )
VALUES
('Aardvark'),
('Bird'),
('Cat'),
('Dog'),
('Turtle'),
('Turtle'),
('Bird'),
('Bird'),
('Cat'),
('Dog'),
('Elephant'),
('Cat'),
('Cat'),
('Dog'),
('Bird'),
('Cat'),
('Turtle'),
('Giraffe'),
('Elephant'),
('Dog')
SELECT ISNULL(CASE A.AnimalName WHEN 'Elephant' THEN 'Other' WHEN 'Giraffe' THEN 'Other' ELSE A.AnimalName END, 'All') AnimalName, COUNT(*) Count
FROM #Animal A
GROUP BY CASE A.AnimalName WHEN 'Elephant' THEN 'Other' WHEN 'Giraffe' THEN 'Other' ELSE A.AnimalName END
WITH ROLLUP
ORDER BY GROUPING ( CASE A.AnimalName WHEN 'Elephant' THEN 'Other' WHEN 'Giraffe' THEN 'Other' ELSE A.AnimalName END ) DESC, AnimalName
DROP TABLE #Animal
Good catch Scott, we all get caught up with false assumtions with the data we are playing with.
I took it one step further and added a line so it will also sort 'Other' to the end of the list
I took it one step further and added a line so it will also sort 'Other' to the end of the list
SELECT ISNULL(CASE A.AnimalName WHEN 'Elephant' THEN 'Other' WHEN 'Giraffe' THEN 'Other' ELSE A.AnimalName END, 'All') AnimalName, COUNT(*) Count
FROM #Animal A
GROUP BY CASE A.AnimalName WHEN 'Elephant' THEN 'Other' WHEN 'Giraffe' THEN 'Other' ELSE A.AnimalName END
WITH ROLLUP
ORDER BY GROUPING ( CASE A.AnimalName WHEN 'Elephant' THEN 'Other' WHEN 'Giraffe' THEN 'Other' ELSE A.AnimalName END ) DESC,
MIN(CASE A.AnimalName WHEN 'Elephant' THEN 2 WHEN 'Giraffe' THEN 2 ELSE 1 END),
AnimalName
select 'ALL' as groupname, count(*) cnt from myanimals
union all
select replace(replace(animalname ,'Giraffe' ,'Other'), 'Elephant' ,'Other'), count(*)
from myanimals group by replace(replace(animalname ,'Giraffe' ,'Other'), 'Elephant' ,'Other')
;
union all
select replace(replace(animalname
from myanimals group by replace(replace(animalname
;
select
case AnimalName
when 'Giraffes' then 'Other'
when 'Elephants' then 'Other'
else AnimalName
end as myAnimalName
from myAnimals
group by case AnimalName
when 'Giraffes' then 'Other'
when 'Elephants' then 'Other'
else AnimalName
end