Menu
Let's say I have two existing tables, "dogs" and "cats":
dog_name | owner
---------+------
Sparky | Bob
Rover | Bob
Snoopy | Chuck
Odie | Jon
cat_name | owner
---------+------
Garfield | Jon
Muffy | Sam
Stupid | Bob
How do I write a query with this output?
owner | num_dogs | num_cats
------+----------+---------
Bob | 2 | 1
Chuck | 1 | 0
Sam | 0 | 1
Jon | 1 | 1
577
We cannot use the WHERE clause with aggregate functions because it works for filtering individual rows. In contrast, HAVING can works with aggregate functions because it is used to filter groups.
In addition to providing information about an entire table, aggregate functions can be used on groups of rows. The GROUP BY clause arranges rows into groups, and aggregate functions return a single value for each group of rows.
And data aggregation is impossible without GROUP BY! Therefore, it is important to master GROUP BY to easily perform all types of data transformations and aggregations. In SQL, GROUP BY is used for data aggregation, using aggregate functions.
The first option is to combine two aggregate functions using a subquery. The subquery is a query within the main query. When creating reports, they are usually found in the SELECT , FROM , or WHERE clauses. In this example, I'll put the subquery in the FROM clause.
select owner, sum(num_dogs), sum(num_cats) from
(select owner, 1 as num_dogs, 0 as num_cats from dogs
union
select owner, 0 as num_dogs, 1 as num_cats from cats)
group by owner
I prefer this one:
select owner
, count(dog_name) dogs
, count(cat_name) cats
from cats FULL OUTER JOIN dogs ON (cats.owner = dogs.owner)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
