Problem using aggregation in MS SQL

[incubeme]

Hi,
I would like to get an aggregation from several different tables, but don't know how to get. I have tried many different options, but no success. Hopefully someone here can help me out?

The setting is:

Table 1(actually a view) - contains a list of persons
P_id
A
B
C

Table2 - A log of posts the persons have made during a day
P_id; Post
A; 1
A; 3
B; 1

Table3 - A log of orders the persons have made during a day
P_id; Orders
A; 2
B; 2
C; 1

So, I want to loop through all Persons in table 1, and count their "activites" shown in table2 and table3 (and 4,5,6 etc, I have 7 tables). The result should be:

P_id; Count(posts); Count(Orders)
A; 2; 2
B; 1; 1
C; 0; 1

Anyone knows how to achieve this?

Thanks,
incubeme

[ck9663]

Hi,
I would like to get an aggregation from several different tables, but don't know how to get. I have tried many different options, but no success. Hopefully someone here can help me out?

The setting is:

Table 1(actually a view) - contains a list of persons
P_id
A
B
C

Table2 - A log of posts the persons have made during a day
P_id; Post
A; 1
A; 3
B; 1

Table3 - A log of orders the persons have made during a day
P_id; Orders
A; 2
B; 2
C; 1

So, I want to loop through all Persons in table 1, and count their "activites" shown in table2 and table3 (and 4,5,6 etc, I have 7 tables). The result should be:

P_id; Count(posts); Count(Orders)
A; 2; 2
B; 1; 1
C; 0; 1

Anyone knows how to achieve this?

Thanks,
incubeme

TRY

select P_id, postings.PostCount, orders.OrderCount
from Table1
left join (select P_id, count(*) PostCount from table2) as postings on table1.p_id = postings.p_id
left join (select P_id, count(*) OrderCount from table3) as Orders on table1.p_id = Orders.p_id

....AND SO ON ....

[incubeme]

Thanks for reply. I tried it out, but got the following mesages:

Column 'table2.P_id' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Column 'table3.P_id' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

When I tried to add: group by table2.P_id, I just got:
The column prefix 'table2.P_id' does not match with a table name or alias name used in the query.

Any clues?

[ck9663]

Thanks for reply. I tried it out, but got the following mesages:

Column 'table2.P_id' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Column 'table3.P_id' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

When I tried to add: group by table2.P_id, I just got:
The column prefix 'table2.P_id' does not match with a table name or alias name used in the query.

Any clues?

yes, it's my fault :)

now, try this:


select P_id, postings.PostCount, orders.OrderCount
from Table1
left join (select P_id, count(*) PostCount from table2 group by p_id) as postings on table1.p_id = postings.p_id
left join (select P_id, count(*) OrderCount from table3 group by p_id) as Orders on table1.p_id = Orders.p_id

...and so on...

[incubeme]

That did the trick! :-) :-) :-)

Thank you very much for your help