Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Counting DISTINCT Values using SQL

Posted on 2013-10-26
7
Medium Priority
?
398 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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

636 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