Perhaps some kind soul could help me out with an SQL I have been trying all
day to get to work, where one colum is just not summing up the way I want it
to.

I have the following data

GRP_SEQ ITEM_SEQ NR_ITEMS PERSONS_SEQ

1 A 2 aa
1 A 2 bb
1 A 2 cc
1 B 3 dd
1 B 3 ee
2 C 1 ff
2 C 1 gg

An item is part of a group. The items are sold in specified numbers and are
associated with persons.

The first result I want to acheive is list each item and the number of items
used by the number of persons associated with the item:

select
GRP_SEQ
,ITEM_SEQ
,sum(ITEM_NR)/count(ITEM_SEQ)
,count(PERSONS_SEQ)
from
GRP
inner join ITEM on ITEM_GRPSEQ=GRP_SEQ
left outer join PERSONS on PERSONS_ITEMSEQ=ITEM_SEQ
where
ITEM_GRPSEQ=GRP_SEQ
group by ITEM_SEQ,GRP_SEQ
order by 2,1

The result is

1 A 2 3
1 B 3 2
2 C 1 2

This works fine.

The next SQL is the one I am having problems with. I want to group the items
by their group and return the total number of items sold for that group of
items and count the number of persons that used the items of that group.
What I have come up with is the following:

select
GRP_SEQ
,???
,count(PERSONS_SEQ)
from
GRP
inner join ITEM on ITEM_GRPSEQ=GRP_SEQ
left outer join PERSONS on PERSONS_ITEMSEQ=ITEM_SEQ
where
ITEM_GRPSEQ=GRP_SEQ
group by GRP_SEQ
order by 1

The result I am expecting to acheive is

1 5 5
2 1 2

but I have not been able to work out how I can I can sum() the ITEM_NR for
each item only once for each item. I could of course modify the SQL to

select
GRP_SEQ
,(select sum(i2.ITEM_NR) from ITEM i2 where i2.ITEM_GRPSEQ=GRP_SEQ)
,count(PERSONS_SEQ)
from
GRP
inner join ITEM i1 on i1.ITEM_GRPSEQ=GRP_SEQ
left outer join PERSONS on PERSONS_ITEMSEQ=i1.ITEM_SEQ
where
i1.ITEM_GRPSEQ=GRP_SEQ
group by GRP_SEQ
order by 1

but there must be some method to achieve the result without using the costly
subselect. Somehow I think there is even something in the first SQL that I
am overseeing that would allow me to replace the
"sum(ITEM_NR)/count(ITEM_SEQ)" with something more elegant.

Perhaps some kind soul could help me out ....

Regards

Rudolf Bargholz

On 18.02.2007 19:50, Rudolf Bargholz wrote:
I don't see why you would need the sub select since you are grouping by
GRP_SEQ already. Simply usage of SUM, GROUP BY and COUNT(DISTINCT
PERSONS_SEQ) should be sufficient.

Did I miss anything?

Regards

robert

On Feb 21, 8:00 pm, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:
GRP_SEQ ITEM_SEQ NR_ITEMS PERSONS_SEQ

1 A 2 aa
1 A 2 bb
1 A 2 cc
1 B 2 dd
1 B 2 ee
2 C 1 ff
2 C 1 gg

You can see this problem is not so easy.
For examplw SUM(DISTINCT NR_ITEMS) would provide incorrect result.

Generally speaking, if more than 3 tables are joined and each rows of
all tables are not matched one by one, SUM and COUNT would be
multipled number of corresponding rows of other tables.
This is one of typical pattern at the time joining more than 3 tables
and taking SUM and/or COUNT.

Here are some examples.
I don't know that if a person appears in same group/ different item,
count them as one or different.
I assumed that they would be counted separately.
1)
SELECT
GRP_SEQ
, SUM_ITEM_NR AS NR_ITEMS
, count(PERSONS_SEQ) AS NR_PERSONS
from
GRP
inner join
(SELECT ITEM_GRPSEQ
, SUM(ITEM_NR) AS SUM_ITEM_NR
FROM ITEM
GROUP BY ITEM_GRPSEQ
) AS itemsum
on itemsum.ITEM_GRPSEQ=GRP_SEQ
inner join
ITEM itemlnk
on itemlnk.ITEM_GRPSEQ=GRP_SEQ
left outer join
PERSONS
on PERSONS_ITEMSEQ=itemlnk.ITEM_SEQ
group by
GRP_SEQ;

2)
SELECT
GRP_SEQ
, SUM(ITEM_NR) AS NR_ITEMS
, SUM(NR_PERSONS) AS NR_PERSONS
from
GRP
inner join
(SELECT ITEM_GRPSEQ
, ITEM_SEQ
, MAX(ITEM_NR) AS ITEM_NR
, count(PERSONS_SEQ) AS NR_PERSONS
FROM ITEM
left outer join
PERSONS
on PERSONS_ITEMSEQ=ITEM_SEQ
GROUP BY
ITEM_GRPSEQ, ITEM_SEQ
) AS items
on ITEM_GRPSEQ=GRP_SEQ
group by
GRP_SEQ;