CODEWITHSUNDEEP
mysqlsql
Ankur Sinha
Ankur Sinha

Reputation: 6639

Query automatically excluding records with no entries

These are my tables: o, p and v corresponding to order, person and village

v corresponds to village

 vk | vname
 1  |  v1
 2  |  v2
 3  |  v3

p corresponds to person in the village. A and B live in V1. C lives in V2.

 pk | pname | vk
 1  | A     | 1
 2  | B     | 1
 3  | C     | 2

o corresponds to order placed by person. Person A from V1 has placed 3 orders, person C from V2 has placed 1 order.

 ok | pk | cost
 1  | 1  | 10
 2  | 1  | 100
 3  | 1  | 20
 4  | 3  | 200

I am trying to find out the average number of orders per person in a village.

I have formed the query but it excludes results from people from those villages who have not placed any orders at all.

My query:

SELECT
    v.vname,
    count(ok) / count(DISTINCT o.pk) AS avg
FROM
    v,
    o,
    p
WHERE
    p.pk = o.pk
AND p.vk = v.vk
GROUP BY
    v.vk;

What I want:

vname |  avg
  v1  |  1.5
  v2  |  1
  v3  |  0

What I am getting: 

  vname |  avg
    v1  |  3
    v2  |  2

It is not considering the third village from where no orders have taken place and no person exists, but I want it to be shown. Also, person B from village 1 has placed no orders and hence, only the orders from person A in village 1 are taken into consideration.

With 3 orders from person A from V1 and 0 from person 2 from V1, the average must be 3/2 but it excludes the other person and shows 3/1 which is 3.

Any leads as to where I am going wrong and how I can perfect the query?

Upvotes: 0

Views: 37

Answers (3)

rocks
rocks

Reputation: 190

Try this

select v.vname, NULLIF(count(o.ok)/count(p.pk),0)
from v
left join p on (v.vk = p.vk)
left join o on (p.pk = o.pk)
group by 1

you may need to handle divide by zero exception i guess

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

Use left join and inner join 

SELECT v.vname, count(ok)/count(distinct o.pk) AS avg 
FROM v
left join o on o.pk = p.pk
inner join p on p.vk = v.vk
group v.vname

Upvotes: 1

Abdul Rasheed
Abdul Rasheed

Reputation: 6709

Try the LEFT JOIN instead of archaic comma join

SELECT v.vname, NULLIF(count(ok)/count(distinct o.pk),0) AS avg 
FROM v
    LEFT JOIN p ON p.vk = v.vk
    LEFT JOIN o ON o.pk = p.pk
GROUP BY v.vname

Upvotes: 0

Related Questions