--고수는 아니고 그냥....
s_name = 'test' 인것들 중에 아래 데이터에서
2008-11-07 main 2 <---- 2가 아니고 1인듯

CREATE TABLE [DBSARANG] (
 [p_name] [varchar] (4) ,
 [s_name] [varchar] (4) ,
 [l_name] [varchar] (8) ,
 [regdate] [datetime]
)
GO

INSERT INTO DBSARANG (p_name,s_name,l_name,regdate) values('0811',           'test',          'main',       '2008-11-06 15:12:55.433')
INSERT INTO DBSARANG (p_name,s_name,l_name,regdate) values('0811',           'test',          'name',       '2008-11-06 15:12:55.433')
INSERT INTO DBSARANG (p_name,s_name,l_name,regdate) values('0811',           'aaa',           'main',       '2008-11-06 15:12:55.433')
INSERT INTO DBSARANG (p_name,s_name,l_name,regdate) values('0811',           'test',          'bottom',     '2008-11-06 15:12:55.433')
INSERT INTO DBSARANG (p_name,s_name,l_name,regdate) values('0811',           'aaa',           'main',       '2008-11-07 15:12:55.433')
INSERT INTO DBSARANG (p_name,s_name,l_name,regdate) values('0811',           'test',          'main',       '2008-11-07 15:12:55.433')
INSERT INTO DBSARANG (p_name,s_name,l_name,regdate) values('0811',           'bbb',           'sub',        '2008-11-07 15:12:55.433')
INSERT INTO DBSARANG (p_name,s_name,l_name,regdate) values('0811',           'test',          'main',       '2008-11-08 15:12:55.433')
INSERT INTO DBSARANG (p_name,s_name,l_name,regdate) values('0811',           'test',          'main',       '2008-11-08 15:12:55.433')

--SELECT * FROM DBSARANG

select substring(convert(varchar,regdate,120),1,10) regdate, l_name, count(l_name) count
   from DBSARANG
where s_name = 'test'
 group by regdate, l_name
 order by regdate

select substring(convert(varchar,regdate,120),1,10) regdate, l_name, count(l_name) count
   from DBSARANG
where s_name = 'aaa'
 group by regdate, l_name
 order by regdate