Sum two levels at the same time.

[Ian Skinner-3]

Can I do this in one query... or am I going to have to do some looping
and counting?

I have two tables joined in a many to many relation.  I would like to
count how many records in table A are joined to table B and how many
joins between table A and table B.  In other words, how many records in
table A have one or more errors and how many errors are there, grouped
by error code.

Table A
use_no
year

Table B
error_code

TableABjoin
use_no
year
error_code

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3186
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Maureen-2]

Assuming that all records in tableabjoin have parents in table A. you
should be able to do this with
 select count(distinct(t.use_no, t.year)) as tableAsum from
tableabjoin t group by t.error_code

note: Select is pseudo code since I don't which database you're using.
 Syntax is not exact.

On Thu, Feb 5, 2009 at 7:37 AM, Ian Skinner <HOF@...> wrote:

>
> Can I do this in one query... or am I going to have to do some looping
> and counting?
>
> I have two tables joined in a many to many relation.  I would like to
> count how many records in table A are joined to table B and how many
> joins between table A and table B.  In other words, how many records in
> table A have one or more errors and how many errors are there, grouped
> by error code.
>
> Table A
> use_no
> year
>
> Table B
> error_code
>
> TableABjoin
> use_no
> year
> error_code

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3187
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Stone, Lori-2]

I have a sql server and mysql background.  I am a total newbie with
using oracle packages so bear with me :)  The DBA's here had not even
heard of the collect function so not getting much help here.  The
package definition compiles fine (declaration).  When I try to compile
the body I keep getting "invalid data type" for PRED_ARRAY.  Anyone have
any idea what I am doing wrong?

I have a package that I have:

Type PRED_REC IS RECORD ( v_pred_id
PROJECT_TASK_PREDECESSORS.PRED_TASK_ID%type);
Type PRED_ARRAY IS TABLE OF PRED_REC INDEX BY BINARY_INTEGER;

In the package body I have:
BEGIN
        SELECT PROJECT_ID, CAST(COLLECT(pred_task_id) as PRED_ARRAY) as
preds
        FROM PROJECT_TASK_PREDECESSORS
        GROUP BY PROJECT_ID
END;

~Lori

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3188
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6