Nested GROUP SQL statement

Question

I have a table MYTABLE. This contains customers and locations.

What Sql statement can I use to find out what customers have more than 1 location and only display on those with more than 1 location? A location can be used for several different customers.

This did not work: this only showed the number of Occurances

SELECT CU _NO, LOC_NO, COUNT(LOC_NO) AS NUMBEROCC
FROM MYTABLE
GROUP BY LOC_NO
HAVING (COUNT(LOC_NO)>1)

The table has more than one row of of the same customer(sorry this was important)

MicSim · Accepted Answer · 2011-07-19 08:19:38Z

2

You need to include all non-aggregated fields of your SELECT clause in the GROUP BY clause, so you have to remove the LOC field and group by CU_NO.

SELECT CU_NO, COUNT(LOC_NO) AS NUMBEROCC
FROM MYTABLE
GROUP BY CU_NO
HAVING (COUNT(LOC_NO)>1)

answered Jul 19, 2011 at 8:19

MicSim

26.9k17 gold badges96 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ypercubeᵀᴹ · Accepted Answer · 2011-07-19 09:05:32Z

1

SELECT m.CU_NO
     , m.LOC_NO
     , grp.NUMBEROCC
FROM MYTABLE AS m
  JOIN
    ( SELECT CU_NO
           , COUNT(DISTINCT LOC_NO) AS NUMBEROCC
      FROM MYTABLE
      GROUP BY CU_NO
      HAVING COUNT(DISTINCT LOC_NO) > 1
    ) AS grp
    ON grp.CU_NO = m.CU_NO
ORDER BY m.CU_NO

answered Jul 19, 2011 at 9:05

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Comments

ypercubeᵀᴹ · Accepted Answer · 2011-07-19 12:04:48Z

1

You should use a window function if you want to get all the locations of all the customers with more than one location:

SELECT t.CU_NO, t.LOC_NO, t.NUMBEROCC
FROM (
    SELECT CU_NO, LOC_NO, COUNT(LOC_NO) OVER (PARTITION BY CU_NO) AS NUMBEROCC
    FROM MYTABLE
) AS t
WHERE t.NUMBEROCC > 1

If you don't care about all the locations, but just the customer ids, then you can use simple group by clause:

SELECT CU_NO
FROM MYTABLE
GROUP BY CU_NO
HAVING COUNT(DISTINCT LOC_NO) > 1

But in both cases you want to group by CU_NO, not by LOC_NO!

SQL-Server implementation of window functions has not (yet!) included COUNT(DISTINCT x) OVER(PARTITION BY x). So, this raises error:

SELECT t.CU_NO, t.LOC_NO, t.NUMBEROCC
FROM (
    SELECT CU_NO, LOC_NO
         , COUNT(DISTINCT LOC_NO) OVER (PARTITION BY CU_NO) AS NUMBEROCC
    FROM MYTABLE
) AS t
WHERE t.NUMBEROCC > 1

Workaround is to use DENSE_RANK() OVER() and MAX() OVER() with one additional query level:

SELECT x.CU_NO, x.LOC_NO, x.NUMBEROCC
FROM (
    SELECT t.CU_NO, t.LOC_NO
         , MAX(DR) OVER(PARTITION BY CU_NO) AS NUMBEROCC
    FROM (
        SELECT CU_NO, LOC_NO
             , DENSE_RANK() OVER (PARTITION BY CU_NO ORDER BY LOC_NO) AS DR
        FROM MYTABLE
    ) AS t
) AS x
WHERE x.NUMBEROCC > 1

edited Jul 19, 2011 at 12:04

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

answered Jul 19, 2011 at 8:23

Petar Ivanov

93.4k11 gold badges85 silver badges95 bronze badges

6 Comments

icecurtain Over a year ago

The problem your first statement shows the CU_NO_ LOC_NO followed by the total number of all customers with that location. the second shows customers that only have one location. Its my fault as the table contains the same customer more than once.( I Simplified but this has caused confusion)

Petar Ivanov Over a year ago

are you sure... that can't be. It's ok to have the same customer more than once, if he has more than one location.

Petar Ivanov Over a year ago

I have modified the aggregate to be COUNT(DISTINCT LOC_NO). This way even you have duplicate row (same custome with same location twice) it will be correct

icecurtain Over a year ago

The Distinct works fantastic for statement 2 but statement 1 causes error Incorrect syntax near 'distinct'.

Petar Ivanov Over a year ago

ah, maybe this is not a valid window function :(

|

Collectives™ on Stack Overflow

Nested GROUP SQL statement

3 Answers 3

Comments

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related