중복된 record 찾는 query, 개수 세는 쿼리

having으로 count(*)에 조건 걸기

select T2.* from (
    select name from dc_baseball_player 
    group by concat(name, birth)
    having count(*) > 1
    ) T1 inner join dc_baseball_player T2 on(T1.name = T2.name)
order by T1.name asc

 

월별 개수세기

select DATE_FORMAT(created_at,"%Y-%m" )as date, count(*) from wallet
where currency = "USD"
group by date

 

 

일별 개수세기

select DATE_FORMAT(created_at,"%Y-%m-%d" )as date, count(*) from wallet
where currency = "EOS"
group by date

 

 

 

DATE_FORMAT(created_at,"%Y-%m-%d" )

이걸 쓰면 날짜 표시형식을 바꿀 수 있다.

 

 

Code라는 컬럼을 Group by한 후 count를 sum()한 값을 알고 싶을 때

select code, count(code) as count, sum(count(code)) OVER() AS total_count 
from hello_table
where type = 'A'
group by code
;

 

end