469,923 Members | 1,828 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,923 developers. It's quick & easy.

Problem using aggregation in MS SQL

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
Jul 31 '07 #1
4 1545
ck9663
2,878 Expert 2GB
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 ....
Jul 31 '07 #2
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?
Jul 31 '07 #3
ck9663
2,878 Expert 2GB
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...
Jul 31 '07 #4
That did the trick! :-) :-) :-)

Thank you very much for your help
Aug 1 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Merlin | last post: by
12 posts views Thread by Bing Wu | last post: by
4 posts views Thread by cmrchs | last post: by
2 posts views Thread by Jozsef Bekes | last post: by
4 posts views Thread by Frederik Vanderhaegen | last post: by
5 posts views Thread by Nice Chap | last post: by
23 posts views Thread by SenthilVel | last post: by
1 post views Thread by ninjutsu28 | last post: by
7 posts views Thread by Bruce One | last post: by
reply views Thread by Karigar | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.