?
Solved

Counting DISTINCT Values using SQL

Posted on 2013-10-26
7
Medium Priority
?
389 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:Michael Franz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:Michael Franz
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
A new era in Cloud training has arrived.

A day that will go down in Cloud history.. But are you ready for it? Will you accept this Cloud challenge?

 

Author Comment

by:Michael Franz
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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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:Michael Franz
ID: 39603228
Thanks for the help I appreciate the detail.
0
 
LVL 93

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

777 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