sql server programming:
[quoted text, click to view]

This should work; it may or not be efficient:

SELECT x.nickname, sum(n) FROM
(select nickname,count(*) n as Reacties
from tblReactie group by nickname
union all
select nickname,count(*) n as Artikels
from tblArtikel group by nickname) as X
GROUP BY nickname


[quoted text, click to view]

Is it possible to make a join which uses 2 subselects?
The 2 subselects have both a field with the same name.
When i try to do this i get syntaxerrors.

Thx
Frederik

an example:

select * from(
(select nickname,count(*) as aantalreacties
from tblReactie
group by nickname) as reacties,
(select nickname,count(*) as aantalartikels
from tblArtikel
group by nickname) as artikels
)

after the join i want to have a table with 4 fields

reacties.nickname | aantalreacties | artikels.nickname | aantalartikels

then i want to have a table with 2 fields as result

all sorts of nicknames | sum(aantalreacties, aantalartikels)

i am allready a step further with the union all:

select nickname,count(*) as Reacties
from tblReactie group by nickname
union all
select nickname,count(*) as Artikels
from tblArtikel group by nickname

now i have this table:

|nickname | reacties|
---------------------
admsnake 13
admsnake 5
fre 11
fre 7
h 2
h 1
kristel 7
kristel 3

and i want this table:

|nickname | reacties|
---------------------
admsnake 18 sum of 13 + 5
fre 18 sum of 11 + 7
h 3 sum of 1 + 2
kristel 10 sum of 7 + 3

how can i make this sum on the places where the nickname is the same?

problem solved:

select aantallen.nickname, sum(aantallen.reacties)
from(select nickname,count(*) as Reacties
from tblReactie group by nickname
union all
select nickname,count(*) as Artikels
from tblArtikel group by nickname)as aantallen
group by aantallen.nickname

Can you give us an example what you are trying to do.
Probably it can be achieved with one subselect with union in it...


[quoted text, click to view]

[quoted text, click to view]


That is the way :)

alternativelly you can make a view with the subselect and then select from
the view.