Solved

Counting DISTINCT Values using SQL

Posted on 2013-10-26
7
380 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
Webinar: MariaDB® Server 10.2: The Complete Guide

Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 

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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

724 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