How to count the items (records) in a joined table in MS SQL?

Hello Experts,

This is difficult for me to explain, but I'll try to be as clear as possible.  I have three tables that I have joined.  I'm using Left Joins right now because I want to see all of the records in VPPD that meet the criteria provided in the WHERE clause.  

I have joined the VPPD table to the VPID table.  Next I need to count the number of records in the VPID table based on POLICY_ID and the criteria in the WHERE clause.  Unfortunately I get a count of all records in the VPID for the Policy_ID regardless of the criteria in the WHERE clause.  

I apologize if the above is confusing.  Please ask any questions that may help clarify.

Here is what I have tried thus far:

 
SELECT VPPD.POLICY_ID,
       VPPD.COUNTY,
       VI.ENTITY_NAME_OR_LAST_NAME,
       VI.FIRST_NAME,
	   VPPD.TOT_LIMIT_OF_LIABILITY,
	   (SELECT COUNT(*) FROM VW_PI_DESC WHERE POLICY_ID = VPPD.POLICY_ID) AS ITEMCOUNT

FROM VW_PP_DESC VPPD
LEFT JOIN VW_PI_DESC VPID
ON VPPD.POLICY_ID=VPID.POLICY_ID

LEFT JOIN VW_IP_DESC VIP
ON VPPD.POLICY_ID=VIP.POLICY_ID
LEFT JOIN VW_INSURED VI
ON VIP.ACCOUNT_ID=VI.ACCOUNT_ID

WHERE (VPPD.EFFECTIVE_DATE<='2015-04-20' AND VPPD.EXPIRATION_DATE>='2015-04-20')
       AND VPID.CLASS_CODE IN ('B06A', 'B06B')
       AND VPPD.COUNTY='CountyA'
GROUP BY VPPD.POLICY_ID,
       VPPD.COUNTY,
       VI.ENTITY_NAME_OR_LAST_NAME,
       VI.FIRST_NAME,
	   VPPD.TOT_LIMIT_OF_LIABILITY

Open in new window


I am using SSMS 2012.

Any help would be greatly appreciated.

Thanks,
J
ferguson_jeraldAsked:
Who is Participating?
 
Walter RitzelSenior Software EngineerCommented:
try this:
SELECT vppd.policy_id
     , vppd.county
     , vi.entity_name_or_last_name
     , vi.first_name
     , vppd.tot_limit_of_liability
     , COUNT(1) OVER (PARTITION BY vppd.policy_id) AS itemcount
  FROM vw_pp_desc vppd
       LEFT JOIN vw_pi_desc vpid ON vppd.policy_id = vpid.policy_id
       LEFT JOIN vw_ip_desc vip ON vppd.policy_id = vip.policy_id
       LEFT JOIN vw_insured vi ON vip.account_id = vi.account_id
 WHERE (vppd.effective_date <= '2015-04-20'
    AND vppd.expiration_date >= '2015-04-20')
   AND vpid.class_code IN ('B06A', 'B06B')
   AND vppd.county = 'CountyA'
GROUP BY vppd.policy_id
       , vppd.county
       , vi.entity_name_or_last_name
       , vi.first_name
       , vppd.tot_limit_of_liability

Open in new window

0
 
ferguson_jeraldAuthor Commented:
Wow - that was fast...and exactly what I needed.  

Thanks!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.