CODEWITHSUNDEEP
sqlpostgresql
Ali
Ali

Reputation: 3

How to create a query to join three tables and make calculations in SQL?

I'm just at the beginning of my SQL studies and can't figure out how to resolve the next problem.

So, there are three tables:

! given tables

The task is: "Get number of pet type per owner" Write a query to generate the result below:

! desired output

The best result I have for the moment:

SELECT owners.OWNER_NAME, COUNT(pets.OWNER_ID) AS pets 
FROM owners
JOIN pets ON owners.ID = pets.OWNER_ID
JOIN pet_type ON pets.TYPE = pet_type.ID 
GROUP BY owners.OWNER_NAME;

It returns first column with owner names and second column with the sum of particular owner pets.

Will appreciate any help.

Upvotes: 0

Views: 43

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

Check this. To Get number of pet type per owner, this is sufficient to join only Pets table with the owners table. A DISTINCT count of Pet.Type will give your desired output.

SELECT
owners.ID,
owners.OWNER_NAME, 
COUNT(DISTINCT pets.TYPE) AS Num_Pet_Type 
FROM owners
INNER JOIN pets ON owners.ID = pets.OWNER_ID
GROUP BY owners.ID,owners.OWNER_NAME;

If you wants number of Pet per type, use this below script-

SELECT
owners.ID,
owners.OWNER_NAME, 
pets.TYPE,
COUNT(*) AS Num_Of_Pet
FROM owners
INNER JOIN pets ON owners.ID = pets.OWNER_ID
GROUP BY owners.ID,owners.OWNER_NAME,pets.TYPE;

Upvotes: 0

forpas
forpas

Reputation: 164099

You need conditional aggregation:

SELECT 
  o.OWNER_NAME, 
  SUM(CASE WHEN t.name = 'CAT' THEN 1 ELSE 0 END) CAT, 
  SUM(CASE WHEN t.name = 'DOG' THEN 1 ELSE 0 END) DOG,
  SUM(CASE WHEN t.name = 'SNAKE' THEN 1 ELSE 0 END) SNAKE
FROM owners o
JOIN pets p ON o.ID = p.OWNER_ID
JOIN pet_type t ON p.TYPE = t.ID 
GROUP BY o.OWNER_NAME;

I use name as the name of the column describing the type in table pet_type. Change it to the actual name of the column.

Upvotes: 1

Related Questions