Solved

Counting DISTINCT Values using SQL

Posted on 2013-10-26
7
346 Views
Last Modified: 2013-10-26
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

0
Comment
Question by:Newbi22
7 Comments
 
LVL 28

Expert Comment

by:Bill Bach
ID: 39602899
Looks good, except for the use of "CustNo" in the SELECT list.  Remove that and the query should run great.
0
 

Author Comment

by:Newbi22
ID: 39602918
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

0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39602947
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

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Newbi22
ID: 39602953
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

0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 39603175
Too many commas.  Change:

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

Open in new window


to:

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

Open in new window

0
 

Author Closing Comment

by:Newbi22
ID: 39603228
Thanks for the help I appreciate the detail.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39603240
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
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now