Nested GROUP SQL statement

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)

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)

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

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