Keeping duplicates using Count and Max with RIGHT OUTER JOIN
Author Message
Keeping duplicates using Count and Max with RIGHT OUTER JOIN

How can all of a table's entries be included in an output set when using
COUNT and MAX?

Given the following Query:
SELECT a.pid AS p, COUNT(c.eid) AS m, MAX(c.eid) AS t
INTO holder
FROM tableC c RIGHT OUTER JOIN
TableA a ON c.pid = a.pid
GROUP BY a.pid

-- How can this include duplicate entries in a.pid?

IF Table A has field a of int
1
2
3
4
1
2

And Table B has

1 1
1 2
1 3
1 8
2 2
2 4
2 6
4 8

And get a result set of
1 3 3
2 3 6
3 0 NULL
4 1 8
1 3 3
2 3 6

Mon, 26 Sep 2005 15:34:48 GMT
Keeping duplicates using Count and Max with RIGHT OUTER JOIN

Kory,

In the future, post table schemas as CREATE TABLEs and sample data as INSERTs.

The following, I think, is the technique that you are looking for, but frankly I have not been able to figure out how you get your output given your input, so if your output is indeed what you're after then you either have to somehow modify the query or better explain how the output relates to the input.

create table A
(
a int NOT NULL
)

create table B
(
c1 int NOT NULL,
c2 int NOT NULL
)
go

insert into A values (1)
insert into A values (2)
insert into A values (3)
insert into A values (4)
insert into A values (1)
insert into A values (2)

insert into B values (1, 1)
insert into B values (1, 2)
insert into B values (1, 3)
insert into B values (1, 8)
insert into B values (2, 2)
insert into B values (2, 4)
insert into B values (2, 6)
insert into B values (4, 8)
go

alter table A add PK int NOT NULL IDENTITY (1, 1)
go

select A.a, cnt, max_c2
from A
join (select A.a, count(*) as cnt , max(b.c2) as max_c2
from A
left outer join B
on (A.a = B.c1)
group by A.a) as x
on (A.a = x.a)
order by A.PK
go

alter table A drop column PK
go

-------------------------------------------
BP Margolin
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:

> How can all of a table's entries be included in an output set when using
> COUNT and MAX?

> Given the following Query:
> SELECT a.pid AS p, COUNT(c.eid) AS m, MAX(c.eid) AS t
> INTO holder
> FROM tableC c RIGHT OUTER JOIN
>  TableA a ON c.pid = a.pid
> GROUP BY a.pid

> -- How can this include duplicate entries in a.pid?

> IF Table A has field a of int
> 1
> 2
> 3
> 4
> 1
> 2

> And Table B has

> 1 1
> 1 2
> 1 3
> 1 8
> 2 2
> 2 4
> 2 6
> 4 8

> And get a result set of
> 1 3 3
> 2 3 6
> 3 0 NULL
> 4 1 8
> 1 3 3
> 2 3 6

Tue, 27 Sep 2005 03:06:01 GMT

 Page 1 of 1 [ 2 post ]

Relevant Pages