CODEWITHSUNDEEP
postgresql
sumit kundan
sumit kundan

Reputation: 173

How to count total number of records after join the three tables in postgresql?

I have a query which gives me total 12408 records after executing but i want this give me total records as count column

select c.complaint_id,c.server_time,c.completion_date,c.road_id,c.photo,c.dept_code,c.dist_code,c.eng_userid,c.feedback_type,c.status,p.dist_name,p.road_name,p.road_dept,e.display_name,e.mobile from complaints as c INNER JOIN pwd_roads as p ON p.road_id=c.road_id INNER JOIN enc_details as e ON CAST(e.enc_code as INTEGER) = p.enccode where c.complaint_id=c.parent_complaint_id and c.dept_code='PWDBnR' and c.server_time between '2018-09-03' and '2018-12-19'

Upvotes: 0

Views: 37

Answers (2)

George S
George S

Reputation: 2161

You can solve this issue using window functions. For example, if you want your first columns to be a count of the total rows done by the SELECT statement:

select count(1) over(range between unbounded preceding and unbounded following) as total_row_count
 , c.complaint_id,c.server_time,c.completion_date,c.road_id,c.photo,c.dept_code,c.dist_code,c.eng_userid,c.feedback_type,c.status,p.dist_name,p.road_name,p.road_dept,e.display_name,e.mobile from complaints as c INNER JOIN pwd_roads as p ON p.road_id=c.road_id INNER JOIN enc_details as e ON CAST(e.enc_code as INTEGER) = p.enccode where c.complaint_id=c.parent_complaint_id and c.dept_code='PWDBnR' and c.server_time between '2018-09-03' and '2018-12-19'

Note that the window function is evaluated before the LIMIT clause if one is used, so if you were to add LIMIT 100 to the query it might give a row count greater than 100 even though a max of 100 rows would be returned.

Upvotes: 0

Muhammad Bilal
Muhammad Bilal

Reputation: 2134

Easiest but not very elegant way to do this is:

select count(*)
from
( 
select c.complaint_id,c.server_time,c.completion_date,c.road_id,c.photo,c.dept_code,c.dist_code,c.eng_userid,c.feedback_type,c.status,p.dist_name,p.road_name,p.road_dept,e.display_name,e.mobile from complaints as c INNER JOIN pwd_roads as p ON p.road_id=c.road_id INNER JOIN enc_details as e ON CAST(e.enc_code as INTEGER) = p.enccode where c.complaint_id=c.parent_complaint_id and c.dept_code='PWDBnR' and c.server_time between '2018-09-03' and '2018-12-19'
)

Upvotes: 0

Related Questions