mssql count, group by 에 대해 질문 드립니다..

Question

create table Orders ( IDX int primary key, C_name varchar(10), O_Date smalldatetime, S_Cost money, S_Sale tinyint)goinsert into Ordersselect 1,'ABC', '2007-01-01', 40 ,10 union allselect 2,'ABC', '2007-01-01', 30 ,10 union allselect 3,'ABC', '2007-01-01', 25 ,5 union allselect 4,'DEF', '2007-01-07', 30 ,15 union allselect 5,'DEF', '2007-01-07', 25 ,20 union allselect 6,'GHI', '2007-01-12', 10 ,5를 검색하면SELECT * FROM ordersGROUP BY로 COUNT를 하면SELECT C_name,CONVERT(VARCHAR, o_date,120) AS O_Date, COUNT(*) AS G_Count FROM orders GROUP BY C_name,O_date이렇게 나오는데 제가 원하는건ABC, DEF, GHI의 첫째줄의 빨간색부분을 S_Cost, S_Sale 가져와서로 하고 싶은데 가능할까요? ㅠㅠ며칠째 구글링하고 이렇게 저렇게 해봐도 도저히 안되네요..

아로마 · Answer

select a.C_name, a.o_date, GameCount, a.s_scost, b.S_SALE from ( select C_name, O_date, count(*) GameCount, max(s_cost) s_scost FROM orders group by C_name, O_date ) a join orders b on a.C_name&#61;b.C_NAME and a.s_scost&#61;b.S_COST ;

Chang · Answer

며칠째 구글링하면서 고생한걸 해결해주시네요 ㅠㅠ너무 감사드립니다..

Chang · Answer

그런데 궁금한게있는데요..s_cost를 max로 해서 가져오면 s_cost10305020이런경우 3번째인 50을 가져오는데 무조건 1번째 값을 가져오는건 어떻게 하는걸까요?

아로마 · Answer

1. select b.idx, a.C_name, a.o_date, GameCount, a.s_scost, b.S_SALE from ( select C_name, O_date, count(*) GameCount, max(s_cost) s_scost FROM orders group by C_name, O_date ) a join orders b on a.C_name&#61;b.C_NAME and a.s_scost&#61;b.S_COST ; 2. select f.idx, a.C_name, a.o_date, GameCount, b.S_COST, b.S_SALE from ( select C_name, O_date, count(*) GameCount FROM orders group by C_name, O_date ) a join orders b on a.C_name&#61;b.C_NAME join ( select min(idx) idx from orders group by C_name ) f on b.idx&#61;f.idx order by c_name asc ;

Chang · Answer

헐.. 너무 감사합니다...ㅠㅠ

mssql count, group by 에 대해 질문 드립니다..

5개의 답변

#group 목록