SQL Server Query to find records with aggregate funct on one column but multiple columns in select clause

Question

Here is the minimized version of the Customer table. There can be customers having same account number mapped to different Group . I am looking to find out customer numbers which are mapped to more than one group. As I was using sybase my query below was working fine. Same query does not work in SQL Server.

Can I get both custAccnt and corresponding custId in one query as below.

select DISTINCT lt.custAccnt, lt.custId from VAL_CUSTOMERS lt 
where lt.eligible = 'Y' group by lt.custAccnt 
having count(distinct lt.custId) > 1

+----------+-----------+---------+----------+
| custName | custAccnt | custId  | eligible |
+----------+-----------+---------+----------+
| Joe      | AB1VU1235 |   43553 | Y        |
| Joe      | AB1VU1235 |  525577 | Y        |
| Lucy     | CDNMY4568 |  332875 | Y        |
| Lucy     | CDNMY4568 |  211574 | Y        |
| Lucy     | CDNMY4568 |  211345 | Y        |
| Manie    | TZMM7S009 |  123890 | Y        |
| Tom      | YFDU1235  | 1928347 | Y        |
| Tom      | YFDU1235  |  204183 | Y        |
| Chef     | TNOTE6573 |  734265 | Y        |
+----------+-----------+---------+----------+

Result :-

+-----------+---------+
| AB1VU1235 |   43553 |
| AB1VU1235 |  525577 |
| CDNMY4568 |  332875 |
| CDNMY4568 |  211574 |
| CDNMY4568 |  211345 |
| YFDU1235  | 1928347 |
| YFDU1235  |  204183 |
+-----------+---------+

SELECT DISTINCT combined with GROUP BY... Usually a bad sign... — jarlh, Apr 07 '17 at 14:01

Charles Bretana · Answer 1 · 2017-04-07T17:18:46.360

0

I think this might work...

"...customer numbers which are mapped to more than one group..." , <-- group is custAcct?

select t.custAccnt, t.custId 
from VAL_CUSTOMERS t 
where (Select count(distinct custAccnt ) 
       from VAL_CUSTOMERS 
       Where custId = t.custId) > 1

The statement "...customer numbers which are mapped to more than one group..." does not say anything about "eligibility", so I did not mention it. If you really meant to say:

"...eligible customer numbers which are mapped to more than one group...", then try this:

select t.custAccnt, t.custId 
from VAL_CUSTOMERS t 
where eligible = 'Y'
  and (Select count(distinct custAccnt ) 
       from VAL_CUSTOMERS 
       Where custId = t.custId) > 1

or, this might be faster... it answers a slightly different, but, (I think) equivalent question,
"find ...eligible customer numbers where there is another row for the same customer number mapped to a different custAccnt ..."

select t.custAccnt, t.custId 
from VAL_CUSTOMERS t 
where eligible = 'Y'
  and exists 
      (Select * from VAL_CUSTOMERS 
       Where custId = t.custId
         and custAccnt != t.custAccnt )

edited Apr 07 '17 at 17:18

answered Apr 07 '17 at 14:03

Charles Bretana

143,358
22
150
216

Sorry. It does not work. Could you tell me why you tried for count(distinct custAccnt) >1 – Suvasis Apr 07 '17 at 14:36
Because you said " customer numbers which are mapped to more than one group " and a "group" is defined by CustAcct, no ? You don't say, explicitly, but *your* query attempt groups by custAccnt ... So what does this query do? – Charles Bretana Apr 07 '17 at 14:39
group --> custId.. I got your point and updates the query. It looks correct. But its taking long long time even if index created on columns. – Suvasis Apr 07 '17 at 15:44
Try the second alternative SQL query and see if it is any faster. Otherwise, without extensive information about the database schema, the data in the database and indices on it, I cannot help further. – Charles Bretana Apr 07 '17 at 17:20

score 0 · Answer 2 · answered Apr 07 '17 at 14:06

0

There are many ways to tackle this. Here are a couple of them that should work.

select lt.custAccnt
    , lt.custId 
from VAL_CUSTOMERS lt 
cross apply
(
    select c.custAccnt
    from VAL_CUSTOMERS c
    where c.custAccnt = lt.custAccnt
    group by c.custAccnt
    having count(*) > 1
) x
where lt.eligible = 'Y'


select lt.custAccnt
    , lt.custId 
from VAL_CUSTOMERS lt 
where lt.eligible = 'Y'
AND lt.custAccnt IN
(
    select c.custAccnt
    from VAL_CUSTOMERS c
    group by c.custAccnt
    having count(*) > 1
)

answered Apr 07 '17 at 14:06

Sean Lange

33,028
3
25
40

Sorry. It does not work. The internal query returning custAccnt which is present multiple times. I am looking for custAccnt with multiple custId – Suvasis Apr 07 '17 at 14:27
Can you explain how this doesn't work? Both of these queries produce EXACTLY the output you stated you are looking for. http://rextester.com/VIRUY88590 – Sean Lange Apr 07 '17 at 14:38

score 0 · Answer 3 · answered Apr 07 '17 at 15:03

In case of duplicates custAccnt and custId in the table, @Sean query won't work.

WITH cte AS(SELECT *
                 , COUNT (custId) OVER (PARTITION BY custAccnt) AS CntcustId
                 , ROW_NUMBER () OVER (PARTITION BY custAccnt, custId ORDER BY custName)    AS Rownum
                 FROM VAL_CUSTOMERS
                 WHERE eligible = 'Y'
)
SELECT custAccnt, custId
FROM cte
WHERE CntcustId>1
AND Rownum = 1;

Using row number to eliminate the duplicates.

score 0 · Answer 4 · answered Apr 10 '17 at 10:58

;WITH cte1
( custName , custAccnt , custId  , eligible )
As
(

 SELECT 'Joe'      ,'AB1VU1235' ,   43553 , 'Y'    UNION ALL
 SELECT 'Joe'      ,'AB1VU1235' ,  525577 , 'Y'    UNION ALL
 SELECT 'Lucy'     ,'CDNMY4568' ,  332875 , 'Y'    UNION ALL
 SELECT 'Lucy'     ,'CDNMY4568' ,  211574 , 'Y'    UNION ALL
 SELECT 'Lucy'     , 'CDNMY4568' ,  211345 , 'Y'   UNION ALL
 SELECT 'Manie'    ,'TZMM7S009' ,  123890 , 'Y'    UNION ALL
 SELECT 'Tom'      ,'YFDU1235'  , 1928347 , 'Y'    UNION ALL
 SELECT 'Tom'      ,'YFDU1235'  ,  204183 , 'Y'    UNION ALL
 SELECT 'Chef'     ,'TNOTE6573' ,  734265 , 'Y'    
  )

,cte2 AS (
    SELECT custName
          ,custAccnt
          ,count(custName) cnt
    FROM cte1
    GROUP BY custName,custAccnt
    )
,cte3 AS (
    SELECT custName
        ,cnt
    FROM cte2 WHERE cnt <> 1
         )

SELECT custAccnt
    ,custId
FROM cte1
WHERE custName IN (
        SELECT custName
        FROM cte3
        )

SQL Server Query to find records with aggregate funct on one column but multiple columns in select clause

4 Answers4