I need a lot of help with a DB2 query

[spanky1968]

Hello,

I'm sure there is someone here way smarter than me. I need help with a big to me query.

I would like to perform a query that gets the information I need and then join a count of some more information to the end of the output. (I know clear as mud)

Here's what I have so far,

select a.rule, b.account, count(*) from Table1 a, Table2 b, Table3 c where c.rule = a.rule and a.month = DEC and a.rule > 0 and b.account_type != '*' group by b.account_type, a.rule;

This is the output,

118 TYPE A 100
118 TYPE B 20
118 TYPE C 200

I would like to add (a join I think) another column at the end of the report to include a count of the month that matches each account type where the rule is less than 1.

I know this would be very easy for someone but it seems to be escaping me.

Thanks in advance

[wellhole]

Hello,

I'm sure there is someone here way smarter than me. I need help with a big to me query.

I would like to perform a query that gets the information I need and then join a count of some more information to the end of the output. (I know clear as mud)

Here's what I have so far,

select a.rule, b.account, count(*) from Table1 a, Table2 b, Table3 c where c.rule = a.rule and a.month = DEC and a.rule > 0 and b.account_type != '*' group by b.account_type, a.rule;

This is the output,

118 TYPE A 100
118 TYPE B 20
118 TYPE C 200

I would like to add (a join I think) another column at the end of the report to include a count of the month that matches each account type where the rule is less than 1.

I know this would be very easy for someone but it seems to be escaping me.

Thanks in advance

One easy way is to nest a select statement

select a.rule, b.account, count(*),

(select count(*) from table y where x.month = y.month and x.account_type = y.account_type and y.rule < 1)

from table1 a, table2 b, ..............................