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.
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
;
Looks good, except for the use of "CustNo" in the SELECT list. Remove that and the query should run great.
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
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
;
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
;
ASKER
Sorry, but still not working....
This works, without the count..
But this will not work....
I am modeling it from the following query that works. It joins I assume because the counting part is from a different table.
This works, without the count..
SELECT
PolRep
FROM In_Force_Policy_Book_of_Business
GROUP BY
PolRep
;
But this will not work....
SELECT
PolRep,
,Count(DISTINCT CustNo) AS count_CustNo
FROM In_Force_Policy_Book_of_Business
GROUP BY
PolRep
;
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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