Link to home
Start Free TrialLog in
Avatar of Michael Franz
Michael Franz

asked on

Counting DISTINCT Values using SQL

Hello,

I have the following code. I am trying to count the number of DISTINCT customers that each PolRep has.


SELECT
      PolRep,
       CustNo
    , count(DISTINCT CustNo) AS Count_CustNo
    
FROM In_Force_Policy_Book_of_Business AS Name
GROUP BY
      PolRep
;

Open in new window

Avatar of Bill Bach
Bill Bach
Flag of United States of America image

Looks good, except for the use of "CustNo" in the SELECT list.  Remove that and the query should run great.
Avatar of Michael Franz
Michael Franz

ASKER

Ok.. I did that and now I get the following

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. Exception SQL State=37000 Exception vendor error code=-3504

SELECT

      PolRep,

     ,count(DISTINCT CustNo) AS count_CustNo
    
FROM In_Force_Policy_Book_of_Business AS Name

GROUP BY
      PolRep

;

Open in new window

Name is a reserved word.  Try this:

SELECT
      PolRep,
     ,Count(DISTINCT CustNo) AS count_CustNo    
FROM In_Force_Policy_Book_of_Business 
GROUP BY
      PolRep
;

Open in new window

Sorry, but still not working....

This works, without the count..

SELECT

      PolRep

FROM In_Force_Policy_Book_of_Business 

GROUP BY
      PolRep
;

Open in new window




But this will not work....

SELECT
      PolRep,
     ,Count(DISTINCT CustNo) AS count_CustNo    
FROM In_Force_Policy_Book_of_Business 
GROUP BY
      PolRep
;

Open in new window




I am modeling it from the following query that works. It joins I assume because the counting part is from a different table.


SELECT
      Carrier
       ClientLastName
    , count(DISTINCT Client_Last_Name) AS count_ClientLastName
    
FROM NewBusinessHeader AS H
INNER JOIN NewBusinessLineItem AS I ON h.NBHeaderID = I.NBHeaderID

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the help I appreciate the detail.
Newbi22,

Glad to help, and thanks for the points.  That said, I think the other two Experts made a contribution here, and a split may have been more appropriate: BillBach was correct in identifying the problem with your SELECT clause, and IrogSinta was correct about your use of a reserved word. in the FROM clause.

If you click Request Attention, you can ask a Moderator to reopen the question so you can re-do it as a split.

Cheers,

Patrick