Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select Statement within a Select Statement ???

Status
Not open for further replies.
cfcProgrammer

cfcProgrammer

Programmer
Mar 1, 2006
88
CA
Hi,

What I am trying to do is calculate the different types of hours per month. When I try to get the weekend hours, the result I get is the total weekend hours for all months but in each row. ???
Code: 
Month  Flights Day Hrs  Night  Partol  Transit  Weekend
1	22	54.00	37.80	74.02	17.75   340.06	
2	22	48.40	25.20	66.91	11.34   340.06	
3	30    103.95   30.15    112.72   21.38   340.06	
4	19	79.30	13.00	87.06	5.24	340.06	
5	23	89.66	13.99	99.56	4.14	340.06
The 340.06 weekend hours is incorrect. This is actually the total weekend hours for the year. ???

When I try to group by within the "weekend hours" select statement I get an error stating

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Here is the query I created.

Code: 
SELECT 
	mn as "Month", count(*) as "No of Flights",
	sum(dayhours) as "Day Hours", sum(nighthours) as "Night Hours",
	sum(patrolhours) as "Patrol Hours", sum(transithours) as "Transit Hours",
	(SELECT
		sum(flighthours)
	 FROM
		flt f join mission m on f.mseq=m.mseq
	 WHERE
	 	convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
	 BETWEEN '01-apr-2007' and '31-mar-2008' 
	 AND 
		datename(dw, convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))) 
	 IN ('Saturday', 'Sunday') 
	 AND
		left(m.mid,4)like 'BKS%') as "WeekEnd Hours",
	 sum(flighthours) as "Flight Hours"
FROM
	flt f join mission m on f.mseq=m.mseq
WHERE 
	convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
BETWEEN
	'01-apr-2007' and '31-mar-2008' 
AND
	left(m.mid,4)like 'BKS%'
GROUP BY mn

Does anyone have any suggestions?

As always, any help or suggestions will be greatly appreciated.

Thank you
Colleen

cfcProgrammer
 
Sort by date Sort by votes
Sure, you'll get ALL Nweekend hourse for the year, just because of your WHERE clause in the sub-select:

WHERE convert(datetime,(convert(varchar(2),f.mn) + '/' +
convert(varchar(2), f.dy) + '/' +
convert(varchar(4),f.yr)))
BETWEEN '01-apr-2007' and '31-mar-2008'

Try:
Code: 
SELECT  mn as "Month",
        count(*) as "No of Flights",
        sum(dayhours) as "Day Hours",
        sum(nighthours) as "Night Hours",
        sum(patrolhours) as "Patrol Hours",
        sum(transithours) as "Transit Hours",
    (SELECT sum(flighthours)
           FROM flt join mission m on flt.mseq=m.mseq
     WHERE f.mn = flt.mn AND f.dy = flt.dy AND f.yr = flt.Yr
           AND datename(dw, convert(datetime,(convert(varchar(2),flt.mn) + '/' + convert(varchar(2), flt.dy) + '/' + convert(varchar(4),flt.yr)))) IN ('Saturday', 'Sunday')
           AND left(m.mid,4)like 'BKS%') as "WeekEnd Hours",
     sum(flighthours) as "Flight Hours"
FROM
    flt f join mission m on f.mseq=m.mseq
WHERE
    convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
BETWEEN
    '01-apr-2007' and '31-mar-2008'
AND
    left(m.mid,4)like 'BKS%'
GROUP BY mn


But I prefer to do that in derived table and then JOIN it:
Code: 
SELECT  mn                            as "Month",
        count(*)                      as "No of Flights",
        sum(dayhours)                 as "Day Hours",
        sum(nighthours)               as "Night Hours",
        sum(patrolhours)              as "Patrol Hours",
        sum(transithours)             as "Transit Hours",
        MAX(COALESCE(Tbl1.WeekEnd,0)) AS "WeekEnd Hours",
        sum(flighthours)              as "Flight Hours"
FROM flt f
join mission m on f.mseq=m.mseq AND m.Mid LIKE 'BKS%'
LEFT JOIN (SELECT Mn, Dy, Yr, MSeq, sum(flighthours)
                  FROM flt
                  join mission m on flt.mseq=m.mseq AND m.Mid LIKE 'BKS%'
           WHERE convert(datetime,(convert(varchar(2),flt.mn) + '/' + convert(varchar(2), flt.dy) + '/' + convert(varchar(4),flt.yr))) BETWEEN '01-apr-2007' and '31-mar-2008'
           AND datename(dw, convert(datetime,(convert(varchar(2),flt.mn) + '/' + convert(varchar(2), flt.dy) + '/' + convert(varchar(4),flt.yr)))) IN ('Saturday', 'Sunday')) Tbl1
ON F.MSeq = Tbl1.Mseq AND f.mn = Tbl1.mn AND f.dy = Tbl1.dy AND f.yr = Tbl1.Yr
WHERE convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
BETWEEN '01-apr-2007' and '31-mar-2008'
GROUP BY mn

NOT TESTED

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
Thank you so much for both ideas. I've tried both however, there's an issue with each that I'm unsure how to resolve.

The first query is expecting the group by to include the day and month even though it is not specified in a select statement... ???? and if I do add these fields into the group by then my result is not what I need.

The second query is giving me a message stating

"No column was specified for column 5 of 'Tbl1'."

I tried to add in the f.sum(flighthours) = flt.sum(flighthours)... a long shot I know but it was worth a try and of course it did not work. How can I get passed this?

again thank you very much for your help with this... believe me it is greatly appreciated.

Colleen

cfcProgrammer
 
Upvote 0 Downvote

It looks like you're just missing an alias for sum(flighthours) in the derived table.

If I'm reading bborisov's code correctly you should change this line:
LEFT JOIN (SELECT Mn, Dy, Yr, MSeq, sum(flighthours)

to this:
LEFT JOIN (SELECT Mn, Dy, Yr, MSeq, sum(flighthours) [bold][WeekEnd][/bold]

Hope that helps,
Pat

 
Upvote 0 Downvote

Sorry, I got the tags mixed up. Change it to:
LEFT JOIN (SELECT Mn, Dy, Yr, MSeq, sum(flighthours) [WeekEnd]

 
Upvote 0 Downvote
Yep,
pmegan is right.
I forgot to alias SUM() column:
Code: 
SELECT  mn                            as "Month",
        count(*)                      as "No of Flights",
        sum(dayhours)                 as "Day Hours",
        sum(nighthours)               as "Night Hours",
        sum(patrolhours)              as "Patrol Hours",
        sum(transithours)             as "Transit Hours",
        MAX(COALESCE(Tbl1.WeekEnd,0)) AS "WeekEnd Hours",
        sum(flighthours)              as "Flight Hours"
FROM flt f
join mission m on f.mseq=m.mseq AND m.Mid LIKE 'BKS%'
LEFT JOIN (SELECT Mn, Dy, Yr, MSeq,
                  sum(flighthours) AS WeekEnd
                  FROM flt
                  join mission m on flt.mseq=m.mseq AND m.Mid LIKE 'BKS%'
           WHERE convert(datetime,(convert(varchar(2),flt.mn) + '/' + convert(varchar(2), flt.dy) + '/' + convert(varchar(4),flt.yr))) BETWEEN '01-apr-2007' and '31-mar-2008'
           AND datename(dw, convert(datetime,(convert(varchar(2),flt.mn) + '/' + convert(varchar(2), flt.dy) + '/' + convert(varchar(4),flt.yr)))) IN ('Saturday', 'Sunday')) Tbl1
ON F.MSeq = Tbl1.Mseq AND f.mn = Tbl1.mn AND f.dy = Tbl1.dy AND f.yr = Tbl1.Yr
WHERE convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
BETWEEN '01-apr-2007' and '31-mar-2008'
GROUP BY mn

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
Thank you both very much... this did resolve the "No column was specified for column 5 of 'Tbl1'." however, now I am getting the same issue as the first query. The flt.mn,flt.dy,flt.yr, flt.mseq are invalid because they are not included in an aggregate function.

????

do I need to save this off into another table then group by the month or is there a way for us to do that within this same sql statement.

Thanks again for all of your help


cfcProgrammer
 
Upvote 0 Downvote
It looks like this is coming from the subquery/derived table. This isn't tested, but it may do the trick:

Code: 
SELECT  mn                            as "Month",
        count(*)                      as "No of Flights",
        sum(dayhours)                 as "Day Hours",
        sum(nighthours)               as "Night Hours",
        sum(patrolhours)              as "Patrol Hours",
        sum(transithours)             as "Transit Hours",
        MAX(COALESCE(Tbl1.WeekEnd,0)) AS "WeekEnd Hours",
        sum(flighthours)              as "Flight Hours"
FROM flt f
join mission m on f.mseq=m.mseq AND m.Mid LIKE 'BKS%'
LEFT JOIN (SELECT Mn, Dy, Yr, MSeq,
                  sum(flighthours) AS WeekEnd
                  FROM flt
                  join mission m on flt.mseq=m.mseq AND m.Mid LIKE 'BKS%'
           WHERE convert(datetime,(convert(varchar(2),flt.mn) + '/' + convert(varchar(2), flt.dy) + '/' + convert(varchar(4),flt.yr))) BETWEEN '01-apr-2007' and '31-mar-2008'
           AND datename(dw, convert(datetime,(convert(varchar(2),flt.mn) + '/' + convert(varchar(2), flt.dy) + '/' + convert(varchar(4),flt.yr)))) IN ('Saturday', 'Sunday')
[red]GROUP BY Mn, Dy, Yr, MSeq[/red]) Tbl1
ON F.MSeq = Tbl1.Mseq AND f.mn = Tbl1.mn AND f.dy = Tbl1.dy AND f.yr = Tbl1.Yr
WHERE convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
BETWEEN '01-apr-2007' and '31-mar-2008'
GROUP BY mn
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
763
SashaBuilder3
SashaBuilder3
grnzbra
  • Locked
  • Question
Create Function Within IF statement. 1
Replies
4
Views
1K
mikrom
mikrom
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
664
Andrzejek
Andrzejek
grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
466
grnzbra
grnzbra
Auguy
  • Locked
  • Question
Using UDF In Select, Bad Idea? 2
Replies
3
Views
293
Auguy
Auguy

Part and Inventory Search

Sponsor

Back
Top