참고하세요.

SELECT REGDATE, SUM([1개]), SUM([2개]), SUM([3개]), SUM([4개]), SUM([5개이상])
FROM
(
SELECT REGDATE, PHONE
, CASE WHEN COUNT(*) = 1 THEN 1 END AS [1개]
, CASE WHEN COUNT(*) = 2 THEN 1 END AS [2개]
, CASE WHEN COUNT(*) = 3 THEN 1 END AS [3개]
, CASE WHEN COUNT(*) = 4 THEN 1 END AS [4개]
, CASE WHEN COUNT(*) > 4 THEN 1 END AS [5개이상]
FROM TABLE
GROUP BY PHONE , REGDATE
) X
GROUP BY REGDATE




> 한지.. 님이 쓰신 글
> ----------------------------------------------------------
> OS : Microsoft Windows 2003 Server / Enterprise Server
> SQL : Microsoft SQL Server 2000 Standard / Enterprise Edition
>
> SELECT Regdate, Phone,Count(*) AS Cnt FROM Table
> GROUP BY Regdate, Phone
> HAVING Count(*) = 1
>
> SELECT Regdate, Phone,Count(*) AS Cnt FROM Table
> GROUP BY Regdate, Phone
> HAVING Count(*) = 2
>
> SELECT Regdate, Phone,Count(*) AS Cnt FROM Table
> GROUP BY Regdate, Phone
> HAVING Count(*) = 3
>
> 이런식으로 데이터를 가져오는데요
> 이게 좀많아요~! 날짜별 한개인녀셕만 가져오는건데
> 1게,2개,3개 이런식으로 데이터를 가져와야 해서
> 이런식으로 처리했습니다.
>
> 하지만 이 갯수를 한쿼리로 보고싶어서 이렇게 처리했습니다.
>
>
>
>
>
> SELECT
> LP.Regdate
> ,(SELECT Count(*)
> FROM
> Table2 UI JOIN
> (
> SELECT Regdate, Phone,Count(*) AS Cnt FROM Table
> WHERE Regdate = LP.Regdate
> GROUP BY Regdate, Phone
> HAVING Count(*) = 1
> ) A ON UI.Phone = A.Phone)
> ,(SELECT Count(*)
> FROM
> Table2 UI JOIN
> (
> SELECT Regdate, Phone,Count(*) AS Cnt FROM Table
> WHERE Regdate = LP.Regdate
> GROUP BY Regdate, Phone
> HAVING Count(*) = 2
> ) A ON UI.Phone = A.Phone)
> ,(SELECT Count(*)
> FROM
> Table2 UI JOIN
> (
> SELECT Regdate, Phone,Count(*) AS Cnt FROM Table
> WHERE Regdate = LP.Regdate
> GROUP BY Regdate, Phone
> HAVING Count(*) = 3
> ) A ON UI.Phone = A.Phone)
> ,(SELECT Count(*)
> FROM
> Table2 UI JOIN
> (
> SELECT Regdate, Phone,Count(*) AS Cnt FROM Table
> WHERE Regdate = LP.Regdate
> GROUP BY Regdate, Phone
> HAVING Count(*) = 4
> ) A ON UI.Phone = A.Phone)
> ,(SELECT Count(*)
> FROM
> Table2 UI JOIN
> (
> SELECT Regdate, Phone,Count(*) AS Cnt FROM Table
> WHERE Regdate = LP.Regdate
> GROUP BY Regdate, Phone
> HAVING Count(*) >= 5
> ) A ON UI.Phone = A.Phone)
> FROM Table LP
> GROUP BY Regdate
>
> 이렇게 하면 결과값이
>
> 날짜 1개 2개 3개 4개 5개이상
> 2009-03-31 0 0 0 0 1
> 2009-03-13 1 1 0 0 0
> 2009-03-23 1 0 0 0 0
> 2009-03-27 1 0 0 0 0
>
> 이런식으로 잘나오는데 뭔가 맘에 들지않네요
> 좀더 좋은 방법없을까요??
>
>
>
>
>
>