order by sum from both tables?

# Thread: order by sum from both tables?

1. Member
Join Date
Dec 1969
Posts
92

## order by sum from both tables?

I found this example on "Union All" from the 4guys site (pasted below). My question is: would it be possible to have a "union all-query" and order the results by a sum which is counted from both tables? Thanks... <BR>--------------------------------------- <BR>SELECT id=convert(varchar(11), au_id), <BR> last_name = au_lname, <BR> table_name = &#039;authors&#039; <BR>FROM authors <BR>Where au_lname = &#039;smith&#039; <BR>UNION ALL <BR>SELECT id=convert(varchar(11), emp_id ), <BR> last_name = lname, <BR> table_name = &#039;employee&#039; <BR>FROM Employee <BR>where lname = &#039;Smith&#039; <BR>ORDER BY last_name <BR>------------------------------- <BR>

2. Senior Member
Join Date
Dec 1969
Posts
569

3. Member
Join Date
Dec 1969
Posts
92

## OK, i'll try to explain

This doesnt work but maybe this shows what Im trying to do:<BR><BR>SELECT year, player, sum(thenumber) as thesum<BR>FROM TableOne <BR>Where year = &#039;2001&#039; <BR><BR>UNION ALL <BR><BR>SELECT year, player, sum(thenumber) as thesum<BR>FROM TableTwo <BR>Where year = &#039;2001&#039; <BR><BR>GROUP BY player<BR>ORDER BY thesum DESC;

4. Senior Member
Join Date
Dec 1969
Posts
415

## Yes

If you can dream up a result set there is almost surely a way to select it, so don&#039;t believe anyone who tells you no.<BR><BR>Maybe like...<BR><BR>Select t.lastname, Sum(t.id) as &#039;theSum&#039;<BR><BR>From (<BR><BR>SELECT id=convert(varchar(11), au_id), <BR>last_name = au_lname, <BR>table_name = &#039;authors&#039; <BR>FROM authors <BR><BR>UNION ALL <BR>SELECT id=convert(varchar(11), emp_id ), <BR>last_name = lname, <BR>table_name = &#039;employee&#039; <BR>FROM Employee <BR>) as t<BR>Group by t.lastname <BR>ORDER BY theSum

5. Senior Member
Join Date
Dec 1969
Posts
415

## RE: Yes

Select t.year, t.player, sum(t.thenumber) as &#039;theSum&#039;<BR>From<BR>(<BR>SELECT year, player, thenumber <BR>FROM TableOne <BR>Where year = &#039;2001&#039; <BR><BR>UNION ALL <BR><BR>SELECT year, player, thenumber <BR>FROM TableTwo <BR>Where year = &#039;2001&#039; <BR>) as t<BR>GROUP BY player <BR>ORDER BY thesum DESC

6. Member
Join Date
Dec 1969
Posts
92

## thanks! think i get it...

I think that will work for what I&#039;m trying to do. Its 11.30 pm over here so I&#039;ll try it tomorrow... Thanks again! <BR><BR>/Perra

7. Member
Join Date
Dec 1969
Posts
92

## thanks, that was an even better example:) <eo

.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•