Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

subquery and group by

Hi,
I am trying to run this subquery but getting this error “Incorrect syntax near the keyword 'from'.
Msg 102, Level 15, State 1, Line 34
Incorrect syntax near 'b'.”
All what I am trying to do is to get the (avg success) by grouping the DateLabel, Hour and ScriptName.  My main detail query is the one at the bottom (b) but would like to group it by hour and scriptname and also get the average Success for every hour.  Pls. help.  thanks

select a.DateLabel, a.DisplayHour, a.ScriptName, a.success 
 from
 
 
 (select DateLabel, DisplayHour, ScriptName, AVG(Success)as success
 group by DateLabel, DisplayHour, ScriptName)a

from
 
 (SELECT 
convert(varchar, TimeStamp, 101) as DateLabel,
	case
	when datepart(hh, TimeStamp) = 0 then '12:00 AM'
	when datepart(hh, TimeStamp) = 12 then '12:00 PM'
	when datepart(hh, TimeStamp) = 13 then '1:00 PM'
	when datepart(hh, TimeStamp) = 14 then '2:00 PM'
	when datepart(hh, TimeStamp) = 15 then '3:00 PM'
	when datepart(hh, TimeStamp) = 16 then '4:00 PM'
	when datepart(hh, TimeStamp) = 17 then '5:00 PM'
	when datepart(hh, TimeStamp) = 18 then '6:00 PM'
	when datepart(hh, TimeStamp) = 19 then '7:00 PM'
	when datepart(hh, TimeStamp) = 20 then '8:00 PM'
	when datepart(hh, TimeStamp) = 21 then '9:00 PM'
	when datepart(hh, TimeStamp) = 22 then '10:00 PM'
	when datepart(hh, TimeStamp) = 23 then '11:00 PM'
	else convert(varchar, datepart(hh, TimeStamp)) + ':00 AM'
	end as DisplayHour,
	 scriptname, success
	from MyDB.dbo.test2
	where scriptname in('3M, '3G)
	and TimeStamp between '2011-09-22' and '2011-09-23'
	and StepName like 'Evaluation-%')b
	
	on b.DateLabel = a.DateLabel
	and b.DisplayHour = a.DisplayHour
	and b.ScriptName = b.ScriptName

Open in new window

0
karinos57
Asked:
karinos57
  • 6
  • 2
  • 2
1 Solution
 
Simone BCommented:
It seems that you're missing quotes. This:

where scriptname in('3M, '3G)

should be this:

where scriptname in('3M', '3G')

You also are not defining the joins. Instead of the second "from" you need to define the join between the two subqueries.
0
 
Simone BCommented:
The attached query parses successfully. I used an inner join, but of course you may need something different:

 
select a.DateLabel, a.DisplayHour, a.ScriptName, a.success 
 from
 
 
 (select DateLabel, DisplayHour, ScriptName, AVG(Success)as success
 group by DateLabel, DisplayHour, ScriptName)a

inner join
 
 (SELECT 
convert(varchar, TimeStamp, 101) as DateLabel,
	case
	when datepart(hh, TimeStamp) = 0 then '12:00 AM'
	when datepart(hh, TimeStamp) = 12 then '12:00 PM'
	when datepart(hh, TimeStamp) = 13 then '1:00 PM'
	when datepart(hh, TimeStamp) = 14 then '2:00 PM'
	when datepart(hh, TimeStamp) = 15 then '3:00 PM'
	when datepart(hh, TimeStamp) = 16 then '4:00 PM'
	when datepart(hh, TimeStamp) = 17 then '5:00 PM'
	when datepart(hh, TimeStamp) = 18 then '6:00 PM'
	when datepart(hh, TimeStamp) = 19 then '7:00 PM'
	when datepart(hh, TimeStamp) = 20 then '8:00 PM'
	when datepart(hh, TimeStamp) = 21 then '9:00 PM'
	when datepart(hh, TimeStamp) = 22 then '10:00 PM'
	when datepart(hh, TimeStamp) = 23 then '11:00 PM'
	else convert(varchar, datepart(hh, TimeStamp)) + ':00 AM'
	end as DisplayHour,
	 scriptname, success
	from MyDB.dbo.test2
	where scriptname in('3M', '3G')
	and TimeStamp between '2011-09-22' and '2011-09-23'
	and StepName like 'Evaluation-%') b
	
	on b.DateLabel = a.DateLabel
	and b.DisplayHour = a.DisplayHour
	and b.ScriptName = b.ScriptName

Open in new window

0
 
karinos57Author Commented:
that is not the issue, that was only typo error.  I put the join between the 2 queries and i got different error like " Invalid column name 'DateLabel, DisplayHour', ScriptName"  thanks
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
karinos57Author Commented:
Buttercup1,
i tried that and i got this error:
"Msg 207, Level 16, State 1, Line 6
Invalid column name 'DateLabel'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'DisplayHour'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'ScriptName'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'DateLabel'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'DisplayHour'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'ScriptName'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'Success'.
0
 
tim_csCommented:
Is this what you were trying to do?
select b.DateLabel, b.DisplayHour, b.ScriptName, b.success  
 from 
    
 (
select DateLabel, DisplayHour, ScriptName, AVG(Success) as success 
from 
 (
SELECT  
convert(varchar, TimeStamp, 101) as DateLabel, 
        case 
        when datepart(hh, TimeStamp) = 0 then '12:00 AM' 
        when datepart(hh, TimeStamp) = 12 then '12:00 PM' 
        when datepart(hh, TimeStamp) = 13 then '1:00 PM' 
        when datepart(hh, TimeStamp) = 14 then '2:00 PM' 
        when datepart(hh, TimeStamp) = 15 then '3:00 PM' 
        when datepart(hh, TimeStamp) = 16 then '4:00 PM' 
        when datepart(hh, TimeStamp) = 17 then '5:00 PM' 
        when datepart(hh, TimeStamp) = 18 then '6:00 PM' 
        when datepart(hh, TimeStamp) = 19 then '7:00 PM' 
        when datepart(hh, TimeStamp) = 20 then '8:00 PM' 
        when datepart(hh, TimeStamp) = 21 then '9:00 PM' 
        when datepart(hh, TimeStamp) = 22 then '10:00 PM' 
        when datepart(hh, TimeStamp) = 23 then '11:00 PM' 
        else convert(varchar, datepart(hh, TimeStamp)) + ':00 AM' 
        end as DisplayHour, 
         scriptname, success 
        from MyDB.dbo.test2 
        where scriptname in('3M', '3G') 
        and TimeStamp between '2011-09-22' and '2011-09-23' 
        and StepName like 'Evaluation-%') a 
group by DateLabel, DisplayHour, ScriptName
) b

Open in new window

0
 
tim_csCommented:
That could be simplified to this.  
select DateLabel, DisplayHour, ScriptName, AVG(Success) as success 
from 
 (
SELECT  
convert(varchar, TimeStamp, 101) as DateLabel, 
        case 
        when datepart(hh, TimeStamp) = 0 then '12:00 AM' 
        when datepart(hh, TimeStamp) = 12 then '12:00 PM' 
        when datepart(hh, TimeStamp) = 13 then '1:00 PM' 
        when datepart(hh, TimeStamp) = 14 then '2:00 PM' 
        when datepart(hh, TimeStamp) = 15 then '3:00 PM' 
        when datepart(hh, TimeStamp) = 16 then '4:00 PM' 
        when datepart(hh, TimeStamp) = 17 then '5:00 PM' 
        when datepart(hh, TimeStamp) = 18 then '6:00 PM' 
        when datepart(hh, TimeStamp) = 19 then '7:00 PM' 
        when datepart(hh, TimeStamp) = 20 then '8:00 PM' 
        when datepart(hh, TimeStamp) = 21 then '9:00 PM' 
        when datepart(hh, TimeStamp) = 22 then '10:00 PM' 
        when datepart(hh, TimeStamp) = 23 then '11:00 PM' 
        else convert(varchar, datepart(hh, TimeStamp)) + ':00 AM' 
        end as DisplayHour, 
         scriptname, success 
        from MyDB.dbo.test2 
        where scriptname in('3M', '3G') 
        and TimeStamp between '2011-09-22' and '2011-09-23' 
        and StepName like 'Evaluation-%') a 
group by DateLabel, DisplayHour, ScriptName

Open in new window

0
 
karinos57Author Commented:
tim_cs:,
this is exactly what i need, thanks but i need one more thing:  how can substract the value 100 from the Avg Success value?  for instance, if the avg success is 75 then i want 100 to be substracted from that
100- 75 = so the final Success value should be 25.  thanks
0
 
karinos57Author Commented:
i did something like this but still getting the same value:
select b.DateLabel, b.DisplayHour, b.ScriptName, b.success,
case when b.success > 0 then  (100 - b.success) else 0 end "Success1"
from
....
0
 
karinos57Author Commented:
actually it is working now. thanks
0
 
karinos57Author Commented:
thnx
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now