5. sql 기본 명령어 -- 다양한 select 문

--GROUP BY  및 HAVING 그리고 집계함수

select userid, amount from buytbl order by userid

--합계

select userid, sum(amount) from buytbl group by userid

--병칭을 사용해준다

select userid as [사용자 아이디],

SUM(amount) AS [총 구매 개수]

from buytbl group by userid

--select 복사할열 into 새로운 테이블 from 기존테이블

select  * into usertbl2 from memos

-- group by 및 HAVING 그리고 집계함수

select userid, amount from table_2 order by userid

--count () : 행의 개수를 센다

SELECT AVG(amount) as [평균 구매개수] from buytbl

select * from buytbl

--25/12

--having  --웨어절 에 함수를 쓰고 싶을때  

select userid as [사용자], sum(price *amount) as [총 구매 금지]

from buytbl

group by userid 

having sum(price *amount) >1000

----------------------------------------

use master

exec sp_attach_db @dbname=N'AdventureWorks', 

@filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf', 

@filename2=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf'

use AdventureWorks

select top(select count(*)/100 from Sales.CreditCard) CreditCardID from Sales.CreditCard

where CardType = 'Vista'

order by ExpYear, ExpMonth

select top(0.1)percent CreditCardID from Sales.CreditCard

where CardType = 'Vista'

order by ExpYear, ExpMonth

-- select count(*)/100 from Sales.CreditCard

use sqldb

use mydatabase

select * from memos

--select 복사할열 into 새로운 테이블 from 기존테이블

select  * into usertbl2 from memos

-- group by 및 HAVING 그리고 집계함수

select userid, amount from table_2 order by userid

----------------------------