How to improve 'Incorrect syntax near the keyword 'select'.'

Question

this query give me 'Incorrect syntax near the keyword 'select'.' look please below bold character area.

declare @date1 smalldatetime, @date2 smalldatetime, @page nvarchar(100) ,@sum int
select @date1='2009-06-06',@date2='2009-06-13',@page='Tüm Sayfalar'
set @sum = select Sum(t.[VISITINGCOUNT]) from
(        
select count(page) as [VISITINGCOUNT],                 
     cast(DATENAME ( year ,DATE)+'-'+DATENAME (month ,DATE)+
     '-'+DATENAME (day ,DATE) as smalldatetime)  as [DATE]               
    from scr_StatisticaLog                
    where Date between @date1 and  @date2  
          and (Page=@page or  @page='Tüm Sayfalar')  and ProcessType='PageView'           
GROUP BY 
cast(DATENAME ( year ,DATE)+
'-'+DATENAME (month ,DATE)+
'-'+DATENAME (day ,DATE) as smalldatetime)) as t    

select 100*(t.[VISITINGCOUNT]/@sum),t.[DATE] from        
(        
select count(page) as [VISITINGCOUNT],                 
     cast(DATENAME ( year ,DATE)+'-'+DATENAME (month ,DATE)+
     '-'+DATENAME (day ,DATE) as smalldatetime)  as [DATE]               
    from scr_StatisticaLog                
    where Date between @date1 and  @date2  
          and (Page=@page or  @page='Tüm Sayfalar')  and ProcessType='PageView'           
GROUP BY 
cast(DATENAME ( year ,DATE)+
'-'+DATENAME (month ,DATE)+
'-'+DATENAME (day ,DATE) as smalldatetime)) as t   

Answer 1

Try changing

set @sum = select Sum(t.[VISITINGCOUNT]) from

to

select @sum = Sum(t.[VISITINGCOUNT]) from

Haven't tested it works though, just that it parses correctly without errors.

Answer 2

I don't believe you can have SET @var = SELECT ...

Try the following instead:

SELECT @sum = Sum(t.[VISITINGCOUNT]) from ...

I would also like to say that seeing as all you are doing is SUM-ing a single column in your sub-query there is no point in doing the group by, or returning the second column.

You are basically counting the number of records in various groups, then adding all the groups together. Far quicker just to count the total number of pages in total if that is all you need.

The following would be much simpler:

SELECT @sum = COUNT(page) FROM scr_StatisticaLog                
   WHERE Date BETWEEN @date1 AND  @date2  
   AND (Page=@page OR  @page='Tüm Sayfalar') AND ProcessType='PageView'

Answer 3

declare @date1 smalldatetime, @date2 smalldatetime, @page nvarchar(100) ,@sum int
select @date1='2009-06-06',@date2='2009-06-13',@page='Tüm Sayfalar'

--Changed here
select @sum = Sum(t.[VISITINGCOUNT]) from
(        
select count(page) as [VISITINGCOUNT],                 
     cast(DATENAME ( year ,DATE)+'-'+DATENAME (month ,DATE)+
     '-'+DATENAME (day ,DATE) as smalldatetime)  as [DATE]               
    from scr_StatisticaLog                
    where Date between @date1 and  @date2  
          and (Page=@page or  @page='Tüm Sayfalar')  and ProcessType='PageView'           
GROUP BY 
cast(DATENAME ( year ,DATE)+
'-'+DATENAME (month ,DATE)+
'-'+DATENAME (day ,DATE) as smalldatetime)) as t    




select 100*(t.[VISITINGCOUNT]/@sum),t.[DATE] from        
(        
select count(page) as [VISITINGCOUNT],                 
     cast(DATENAME ( year ,DATE)+'-'+DATENAME (month ,DATE)+
     '-'+DATENAME (day ,DATE) as smalldatetime)  as [DATE]               
    from scr_StatisticaLog                
    where Date between @date1 and  @date2  
          and (Page=@page or  @page='Tüm Sayfalar')  and ProcessType='PageView'           
GROUP BY 
cast(DATENAME ( year ,DATE)+
'-'+DATENAME (month ,DATE)+
'-'+DATENAME (day ,DATE) as smalldatetime)) as t

Answer 4

If you insist on sticking with SET, then wrap the entire subquery in parentheses ()

set @sum = ( select Sum(t.[VISITINGCOUNT]) from ....... )

otherwise change the variable assignment to a 'SELECT @var = ' as suggested by other answers

Answer 5

If you are executing this from SSMS, you should try enabling the "SQLCMD Mode" from the menu before executing the script.