Grouping with a full join

Hi,
I would like to know how to group the Amount of both tables while maintaing
all Ids.
-- Correct results for Table1
select
Table1.id
,sum(Table1.Amount) as AmountTable1
from Table1
group by Table1.id
order by 1
-- Correct results for Table2
select
Table2.id
,sum(Table2.Amount) as AmountTable2
from Table2
group by Table2.id
order by 1
-- How do I combine both results?
select
Table1.id
,sum(Table1.Amount) + sum(Table2.Amount) as AmountBoth
from Table1
left join Table2 on Table1.id = Table2.id
group by Table1.id
order by 1
/*
create table Table1 (Id int, Amount int)
create table Table2 (Id int, Amount int)
insert Table1 select 1, 100
insert Table1 select 2, 200
insert Table1 select 3, 300
insert Table1 select 4, 400
insert Table2 select 5, 500
insert Table2 select 2, 100
insert Table2 select 4, 400
insert Table2 select 6, 600
--drop table Table1
--drop table Table2
*/
---
select
coalesce(Table1.id,Table2.id) as id
,sum(coalesce(Table1.Amount,0)) + sum(coalesce(Table2.Amount,0)) as
AmountBoth
from Table1
full outer join Table2 on Table1.id = Table2.id
group by coalesce(Table1.id,Table2.id)
order by 1|||Great, thank you!
<markc600@.hotmail.com> wrote in message
news:1146119352.959456.161230@.t31g2000cwb.googlegroups.com...
>
> select
> coalesce(Table1.id,Table2.id) as id
> ,sum(coalesce(Table1.Amount,0)) + sum(coalesce(Table2.Amount,0)) as
> AmountBoth
> from Table1
> full outer join Table2 on Table1.id = Table2.id
> group by coalesce(Table1.id,Table2.id)
> order by 1
>|||You should be aware that this solution works when there is a one to
one relation ship between the two tables, but not if there is a one to
many (or many to many) relationship.
Here are two alternatives that avoid that problem.
SELECT id, sum(Amount) as Amount
FROM (select id, sum(Amount) as Amount
from Table1
group by id
UNION ALL
select id, sum(Amount) as Amount
from Table1
group by id) as Combo
GROUP BY id
ORDER BY 1
SELECT COALESCE(T1.id,T2.id),
T1.Amount + T2.Amount as Amount
FROM (select id, sum(Amount) as Amount
from Table1
group by id) as T1
FULL OUTER
JOIN (select id, sum(Amount) as Amount
from Table1
group by id) as T2
ON T1.id = T2.id
ORDER BY 1
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 09:53:11 +0300, "Yan" <yanive@.rediffmail.com>
wrote:

>Great, thank you!
>
><markc600@.hotmail.com> wrote in message
>news:1146119352.959456.161230@.t31g2000cwb.googlegroups.com...
>

grouping question

hey all,
i have 2 tables. i'm grouping on table1 i'd like to join this to table2
which would create 1 to 1 relationship. when add extra fields from table2 it
forces me to group by these fields as well. Can someone please explain this
concept to me?
thanks,
rodcharCould you give better specs?
http://www.aspfaq.com/5006
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:DAD412E6-9F72-42D2-8220-A8905E863944@.microsoft.com...
> hey all,
> i have 2 tables. i'm grouping on table1 i'd like to join this to table2
> which would create 1 to 1 relationship. when add extra fields from table2
> it
> forces me to group by these fields as well. Can someone please explain
> this
> concept to me?
> thanks,
> rodchar|||Can you post what you are doing to have an idea of what you are talking abou
t?
AMB
"rodchar" wrote:

> hey all,
> i have 2 tables. i'm grouping on table1 i'd like to join this to table2
> which would create 1 to 1 relationship. when add extra fields from table2
it
> forces me to group by these fields as well. Can someone please explain thi
s
> concept to me?
> thanks,
> rodchar|||rodchar wrote:
> hey all,
> i have 2 tables. i'm grouping on table1 i'd like to join this to
> table2 which would create 1 to 1 relationship. when add extra fields
> from table2 it forces me to group by these fields as well. Can
> someone please explain this concept to me?
> thanks,
> rodchar
All columns must appear in a group by clause unless you are using an
aggregate. From BOL: "When GROUP BY is specified, either each column in
any non-aggregate expression in the select list should be included in
the GROUP BY list, or the GROUP BY expression must match exactly the
select list expression."
If you were allowed to leave a column off the Group By clause, what
value would SQL Server use for the result set (assuming there were
multiple matches)?
You may be able to use a derived table to do what you want, but as Aaron
mentioned, we need some more details.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||The GROUP BY clause is explained here:
http://msdn.microsoft.com/library/d...r />
_9sfo.asp
ML|||thanks David and everyone this helped.
"David Gugick" wrote:

> rodchar wrote:
> All columns must appear in a group by clause unless you are using an
> aggregate. From BOL: "When GROUP BY is specified, either each column in
> any non-aggregate expression in the select list should be included in
> the GROUP BY list, or the GROUP BY expression must match exactly the
> select list expression."
> If you were allowed to leave a column off the Group By clause, what
> value would SQL Server use for the result set (assuming there were
> multiple matches)?
> You may be able to use a derived table to do what you want, but as Aaron
> mentioned, we need some more details.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Group by With count

Hi,
Iam using a count along with a group by condition. (Eg., Select count(col1),
col1 from table1 where col1 = <value> group by col1)
If I run the query and if no matching records if found the result doesnt sho
w anything. Why is this so?. But if I give
count(column) without the group by condition then a single record is fetched
based on no of records..
I not able to really see why the first one is not returning any records if t
he condition does not match.
Thanx in advance
regards
MaheshBasically, when the condition does not match, there is nothing is count (it
is a empty result set), which causes nothing to be displayed.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Mahesh" <anonymous@.discussions.microsoft.com> wrote in message
news:0E67C7CE-7744-41D3-B733-17E6BF42E9E4@.microsoft.com...

quote:

---

> Hi,
> Iam using a count along with a group by condition. (Eg., Select

---

count(col1),col1 from table1 where col1 = <value> group by col1)

quote:

---

> If I run the query and if no matching records if found the result doesnt

---

show anything. Why is this so?. But if I give

quote:

---

> count(column) without the group by condition then a single record is

---

fetched based on no of records..

quote:

---

> I not able to really see why the first one is not returning any records if

---

the condition does not match.

quote:

---

>
> Thanx in advance
> regards
> Mahesh
>

---

|||If you want to see the count and the value of Col1 then you can do so like
this:
SELECT COUNT(*), <value>
FROM Table1
WHERE col1 = <value>
David Portas
--
Please reply only to the newsgroup
--

Group by With count

Hi,
Iam using a count along with a group by condition. (Eg., Select count(col1),col1 from table1 where col1 = <value> group by col1)
If I run the query and if no matching records if found the result doesnt show anything. Why is this so?. But if I give
count(column) without the group by condition then a single record is fetched based on no of records..
I not able to really see why the first one is not returning any records if the condition does not match.
Thanx in advance
regards
MaheshBasically, when the condition does not match, there is nothing is count (it
is a empty result set), which causes nothing to be displayed.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Mahesh" <anonymous@.discussions.microsoft.com> wrote in message
news:0E67C7CE-7744-41D3-B733-17E6BF42E9E4@.microsoft.com...
> Hi,
> Iam using a count along with a group by condition. (Eg., Select
count(col1),col1 from table1 where col1 = <value> group by col1)
> If I run the query and if no matching records if found the result doesnt
show anything. Why is this so?. But if I give
> count(column) without the group by condition then a single record is
fetched based on no of records..
> I not able to really see why the first one is not returning any records if
the condition does not match.
>
> Thanx in advance
> regards
> Mahesh
>|||If you want to see the count and the value of Col1 then you can do so like
this:
SELECT COUNT(*), <value>
FROM Table1
WHERE col1 = <value>
--
David Portas
--
Please reply only to the newsgroup
--

Group By then Concatenate strings

Hi There,
is it possible to Concatenate strings using group by
Select Concatenate (Name) from table1 group by dept
Thanks
GaneshHi, Ganesh
See: http://www.aspfaq.com/show.asp?id=2529
Razvan|||Ganesh
Please tell us what is the result ?
CREATE TABLE #test
(
FirstName VARCHAR(100) NOT NULL,
LastName VARCHAR(100) NOT NULL,
Dept INT
)
INSERT INTO #test VALUES ('Bill','Smith',100)
INSERT INTO #test VALUES ('John','Lee',100)
INSERT INTO #test VALUES ('David','Litvak',200)
INSERT INTO #test VALUES ('Alex','Boon',200)
SELECT * ,FirstName+' '+LastName AS SurName
FROM #test
"Ganesh" <ganeshsankaran@.hotmail.com> wrote in message
news:C222CA3D-346A-4A95-AB33-4EDA626E7B9A@.microsoft.com...
> Hi There,
> is it possible to Concatenate strings using group by
> Select Concatenate (Name) from table1 group by dept
> --
> Thanks
> Ganesh