1

enter image description here

I have tried below query but it shows wrong count

 select 
  count(DISTINCT schools.id) as total_schools,
  (select SUM(CASE when schools.is_vts_enabled=true then 1 else 0 END) as vts_enabled from schools where schools.cluster_id = crc_users.id) as vts_enabled,
  SUM(CASE when schools.is_primary=true then 1 else 0 end) as total_primary_schools,
  SUM(CASE when schools.is_secondary=true then 1 else 0 end) as total_secondary_schools,
  COUNT(DISTINCT drivers.id) as total_drivers,
  COUNT(DISTINCT drivers.school_id) as total_schools_with_drivers,
  COUNT(DISTINCT vehicles.id) as total_vehicles,
  COUNT(DISTINCT vehicle_students.vehicle_id) as total_vehicles_with_students
from 
  drc_users 
  left join brc_users on drc_users.id = brc_users.district_id 
  left join crc_users on crc_users.block_id = brc_users.id 
  left join schools on schools.cluster_id = crc_users.id
  left JOIN drivers on drivers.school_id = schools.id
  left join vehicles on vehicles.school_id = schools.id
  left join vehicle_students on vehicle_students.vehicle_id = vehicles.id
 where 
  drc_users.district_id='2401'
  group by drc_users.id

I wanted to find total counts from related tables.

drc_users->brc_users->crc_users->schools->drivers,vehicles->vehicle_students

I get wrong count in column "total_secondary_schools"

Here is sample link - http://sqlfiddle.com/#!9/ed0bf2/3

8
  • Common join multiplying. DISTINCT cannot fix this in a half of cases. So aggregate separate table or minimal joined tableset in the subquery then join the rest of tables or another subqueries.
    – Akina
    Mar 15, 2023 at 4:55
  • Thank you for comment. But can you show some examples related to this?
    – Priyal
    Mar 15, 2023 at 5:09
  • Not SELECT COUNT(DISTINCT c.x) FROM a JOIN b JOIN c but SELECT c.cnt FROM a JOIN b JOIN (SELECT COUNT(x) AS cnt FROM c) AS c.
    – Akina
    Mar 15, 2023 at 5:10
  • Yes, I have tried this. but it shows the count for all records from table C. I want only related records.
    – Priyal
    Mar 15, 2023 at 5:17
  • In the subquery you must select not only COUNT but also all columns which are used for joining and filtering in outer query, and apply needed GROUP BY expression. So you'd obtain not total amount but amounts per needed groups.
    – Akina
    Mar 15, 2023 at 5:37

1 Answer 1

0

Be aware: JOIN is performed before COUNT.

This syntax is usually what one wants:

SELECT
    ( SELECT COUNT(*) FROM foo )  AS foo_ct,
    ( SELECT COUNT(*) FROM bar )  AS bar_ct, ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.