SQL: How to only apply where to certain fields?-CodePudding

I need to apply a where clause to my count selects but not to the names, room code, or date.

Here is my query currently which applies the where to the whole statement:

SELECT
  l.name AS room_name,
  l.room_code,
  COUNT(res.*) AS nofixeditems,
  COUNT(m.*) AS noinmaintenance,
  rc.actiondate AS last_checked,
  concat(u.firstname, ' ', u.surname) AS checked_by
FROM
  resources res
  FULL JOIN room_checklist rc ON (rc.assetid = res.aid)
  FULL JOIN users u ON (u.uid = rc.actionuserid)
  FULL JOIN locations l ON (l.locationid = rc.locationid)
  FULL JOIN resource_locations rl ON (rl.location_id_fixed = l.locationid)
  FULL JOIN maintenance.maintenance_log m ON (res.aid = m.asset_id)
WHERE
  res.archived IS NOT TRUE AND 
  rl.location_id_fixed = l.locationid::bigint AND 
  res.hide_from_room_checklist IS NOT TRUE
GROUP BY
  l.name,
  l.room_code,
  rc.actiondate,
  checked_by

I need it to display the whole list of room names, codes, etc while displaying null on the counts where the data isnt available.

CodePudding user response：

If you mean set condition for calculating in aggregation function you can use filter (where conditions...)

Postgres document

SELECT
  l.name AS room_name,
  l.room_code,
  COUNT(res.*) FILTER (WHERE ...) AS nofixeditems,
  COUNT(m.*) FILTER (WHERE ...) AS noinmaintenance,
  rc.actiondate AS last_checked,
  concat(u.firstname, ' ', u.surname) AS checked_by
FROM
  resources res
  FULL JOIN room_checklist rc ON (rc.assetid = res.aid)
  FULL JOIN users u ON (u.uid = rc.actionuserid)
  FULL JOIN locations l ON (l.locationid = rc.locationid)
  FULL JOIN resource_locations rl ON (rl.location_id_fixed = l.locationid)
  FULL JOIN maintenance.maintenance_log m ON (res.aid = m.asset_id)
WHERE
  res.archived IS NOT TRUE AND 
  rl.location_id_fixed = l.locationid::bigint AND 
  res.hide_from_room_checklist IS NOT TRUE
GROUP BY
  l.name,
  l.room_code,
  rc.actiondate,
  checked_by

CodePudding user response：

try using the subQuery

SELECT
  l.name AS room_name,
  l.room_code,
  (Select COUNT(*) from resources where ID = res.id AND ..) AS nofixeditems,
  (Select COUNT(*) from maintenance.maintenance_log where ID = m.id AND ..) AS noinmaintenance,
  rc.actiondate AS last_checked,
  concat(u.firstname, ' ', u.surname) AS checked_by
FROM
  resources res
  FULL JOIN room_checklist rc ON (rc.assetid = res.aid)
  FULL JOIN users u ON (u.uid = rc.actionuserid)
  FULL JOIN locations l ON (l.locationid = rc.locationid)
  FULL JOIN resource_locations rl ON (rl.location_id_fixed = l.locationid)
  FULL JOIN maintenance.maintenance_log m ON (res.aid = m.asset_id)
WHERE
  res.archived IS NOT TRUE AND 
  rl.location_id_fixed = l.locationid::bigint AND 
  res.hide_from_room_checklist IS NOT TRUE
GROUP BY
  l.name,
  l.room_code,
  rc.actiondate,
  checked_by