|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
December 7th, 2003, 11:32 AM
|
|||
|
|||
|
How do I concatenate two select statements?
How do I concatenate two select statements? e.g. Select SELECT LastName,FirstName FROM Persons WITH SELECT SUM(Age) FROM Persons
Thanx 
|
|
#2
December 7th, 2003, 01:24 PM
|
||||
|
||||
|
from your example, it looks like you want the lastname/firstname of every row in the table, as well as the sum of all their ages, which is just one row
there's only one way to do this, and you may not like it -- Code:
select ' ' as flag
, lastname, firstname, age
from persons
union all
select 'total:'
, ' ', ' ', sum(age)
from persons
order
by 1, 2, 3
rudy
|
|
#3
December 7th, 2003, 02:02 PM
|
|||
|
|||
|
Actually what I want to do is combine this two statements:
SqlJunk = " SELECT SUM(MSD+Methods+Analysis+Architectures+Models+Studio+Elective1+Elective2+Elective3) As PersonTotals FROM tblpersons " SqlJunk = SqlJunk & " WHERE User_Id=" & Request.Form("DaInBox") SqlJunk = "SELECT FirstName, LastName, WeekNo, ProgramName, StudioTeamName FROM tblpersons" SqlJunk = SqlJunk & " WHERE User_Id=" & Request.Form("DaInBox") Sorry for not being specific. Thanx
|
|
#4
December 7th, 2003, 04:39 PM
|
||||
|
||||
|
judging from the WHERE clauses, and assuming that User_Id is unique, you do not need to use SUM(), which usually operates on multiple rows
that is, it will work on one row too, but there's no need to use it at all therefore you can simple combine the SELECTs of both queries -- Code:
select msd + methods + analysis + architectures
+ models + studio + elective1 + elective2
+ elective3 as persontotals
, firstname, lastname, weekno, programname, studioteamname
from tblpersons
where user_id = value
rudy
|
|
#5
December 7th, 2003, 09:11 PM
|
|||
|
|||
|
WHEN I USE THIS STATEMENT(the one you send me)
If Request.Form("TypeSearch") = "User_Id" Then SqlJunk = "SELECT MSD+Methods+Analysis+Architectures+Models+Studio+Elective1+Elective2+Elective3 As PersonTotals, FirstName, LastName, WeekNo, ProgramName, StudioTeamName FROM tblpersons" SqlJunk = SqlJunk & " WHERE User_Id=" & Request.Form("DaInBox") End If I GET THE FOLLOWING RESULTS Here are the results of your search: FirstName LastName WeekNo ProgramName StudioTeamName Maggy Lae 1 MSE PMC PersonTotals = PersonTotals doesn't have a value. I want to get the SUM of all those fields(which are numbers) and the Firstname.....are text. Thanx
|
|
#6
December 7th, 2003, 09:51 PM
|
||||
|
||||
|
again, SUM is an aggregate function, operating on multiple rows
you just want simple addition now, one thing that could be causing your problem is that one of the values is null okay, therefore, if you want to add them up, you have to guard against nulls -- Code:
select coalesce(MSD,0)
+ coalesce(Methods,0)
+ coalesce(Analysis,0)
+ coalesce(Architectures,0)
+ coalesce(Models,0)
+ coalesce(Studio,0)
+ coalesce(Elective1,0)
+ coalesce(Elective2,0)
+ coalesce(Elective3,0) As PersonTotals
, firstname
...
|
|
#7
December 8th, 2003, 01:28 AM
|
|||
|
|||
|
Thankx a lot....looks like I was only missing to write PersonTotals from the Recordset....I included another column (PersonTotals) so that it can write the totals there......the following statement worked out perfectly.
Here is what I get: Here are the results of your search: FirstName LastName WeekNo ProgramName TeamName Total Maggy Lae 1 MSE PMC 47 Thankx again 
|
|
| Viewing: Dev Shed Forums > Databases > Database Management > How do I concatenate two select statements? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
