I have three tables


ALIGNMENT
----------------
| store | region |
----------------
| 100 | 6 |
----------------
| 104 | 6 |
----------------
| 109 | 6 |
----------------

TABLE2
----------------
| store | reason |
----------------
| 100 | 1 |
----------------
| 104 | 1 |
----------------
| 100 | 3 |
----------------
| 109 | 3 |
----------------

TABLE2_TXT
---------------------
| reason | reason_txt |
---------------------
| 1 | small |
---------------------
| 2 | medium |
---------------------
| 3 | large |
---------------------
| 4 | x-large |
---------------------



I need results that look like this:


--------------------------------
| Region | Reason | Reason Count |
--------------------------------
| 6 | 1 | 2 |
--------------------------------
| 6 | 2 | 0 |
--------------------------------
| 6 | 3 | 2 |
--------------------------------
| 6 | 4 | 0 |
--------------------------------


Can anyone help with this please? I have to use DB2 and cannot do any
table changes. I have been at this for a couple days and can't seem to
get the results I need.

At the risk of getting it horribly wrong and embarrassing myself no
end, try this:

SELECT A.REGION,
T2.REASON,
COUNT(T2.REASON)
FROM ALIGNMENT AS A
INNER JOIN TABLE2 AS T2 ON T2.STORE = A.STORE
GROUP BY A.REGION, T2.REASON
ORDER BY A.REGION, T2.REASON;

Your problem is that you need both an inclusive (row count) and
exclusive (count = 0) total of occurences of reason code The requirement
for both immediately lends itself to a UNION ALL with separate
statements to generate the two results. UDB 8.2 will do this with the
either of the two following queries:

select a.region as "Region",b.reason as "Reason",count(*) as "Region Count"
from alignment a
join table2 b
on b.store = a.store
group by a.region,b.reason
union all
select a.region as "Region" ,c.reason as "Reason",min(0) as "Region Count"
from alignment a, table2_txt c
where not exists
(select * from table2 b
where b.reason = c.reason)
group by a.region,c.reason
order by "Region","Reason"
;

with t1 as
(select a.region,b.reason
from alignment a
full outer join table2_text b
on 1=1
group by a.region,b.reason
)
select a.region as "Region",a.reason as "Reason"
,count(c.store) as "Region Count"
from t1 a
left outer join table2 c
on c.reason = a.reason
group by a.region,a.reason
;



If this is a classroom exercise, then using these answers would be
considered plagiarism.

Phil Sherman


shanevanle@gmail.com wrote:
> I have three tables
>
>
> ALIGNMENT
> ----------------
> | store | region |
> ----------------
> | 100 | 6 |
> ----------------
> | 104 | 6 |
> ----------------
> | 109 | 6 |
> ----------------
>
> TABLE2
> ----------------
> | store | reason |
> ----------------
> | 100 | 1 |
> ----------------
> | 104 | 1 |
> ----------------
> | 100 | 3 |
> ----------------
> | 109 | 3 |
> ----------------
>
> TABLE2_TXT
> ---------------------
> | reason | reason_txt |
> ---------------------
> | 1 | small |
> ---------------------
> | 2 | medium |
> ---------------------
> | 3 | large |
> ---------------------
> | 4 | x-large |
> ---------------------
>
>
>
> I need results that look like this:
>
>
> --------------------------------
> | Region | Reason | Reason Count |
> --------------------------------
> | 6 | 1 | 2 |
> --------------------------------
> | 6 | 2 | 0 |
> --------------------------------
> | 6 | 3 | 2 |
> --------------------------------
> | 6 | 4 | 0 |
> --------------------------------
>
>
> Can anyone help with this please? I have to use DB2 and cannot do any
> table changes. I have been at this for a couple days and can't seem to
> get the results I need.
>

minor correction - the second query has one table name wrong, it should
be table2_txt, not table2_text.

Phil Sherman

Phil Sherman wrote:
> Your problem is that you need both an inclusive (row count) and
> exclusive (count = 0) total of occurences of reason code The requirement
> for both immediately lends itself to a UNION ALL with separate
> statements to generate the two results. UDB 8.2 will do this with the
> either of the two following queries:
>
> select a.region as "Region",b.reason as "Reason",count(*) as "Region Count"
> from alignment a
> join table2 b
> on b.store = a.store
> group by a.region,b.reason
> union all
> select a.region as "Region" ,c.reason as "Reason",min(0) as "Region Count"
> from alignment a, table2_txt c
> where not exists
> (select * from table2 b
> where b.reason = c.reason)
> group by a.region,c.reason
> order by "Region","Reason"
> ;
>
> with t1 as
> (select a.region,b.reason
> from alignment a
> full outer join table2_text b
> on 1=1
> group by a.region,b.reason
> )
> select a.region as "Region",a.reason as "Reason"
> ,count(c.store) as "Region Count"
> from t1 a
> left outer join table2 c
> on c.reason = a.reason
> group by a.region,a.reason
> ;
>
>
>
> If this is a classroom exercise, then using these answers would be
> considered plagiarism.
>
> Phil Sherman
>
>
> shanevanle@gmail.com wrote:
>> I have three tables
>>
>>
>> ALIGNMENT
>> ----------------
>> | store | region |
>> ----------------
>> | 100 | 6 |
>> ----------------
>> | 104 | 6 |
>> ----------------
>> | 109 | 6 |
>> ----------------
>>
>> TABLE2
>> ----------------
>> | store | reason |
>> ----------------
>> | 100 | 1 |
>> ----------------
>> | 104 | 1 |
>> ----------------
>> | 100 | 3 |
>> ----------------
>> | 109 | 3 |
>> ----------------
>>
>> TABLE2_TXT
>> ---------------------
>> | reason | reason_txt |
>> ---------------------
>> | 1 | small |
>> ---------------------
>> | 2 | medium |
>> ---------------------
>> | 3 | large |
>> ---------------------
>> | 4 | x-large |
>> ---------------------
>>
>>
>>
>> I need results that look like this:
>>
>>
>> --------------------------------
>> | Region | Reason | Reason Count |
>> --------------------------------
>> | 6 | 1 | 2 |
>> --------------------------------
>> | 6 | 2 | 0 |
>> --------------------------------
>> | 6 | 3 | 2 |
>> --------------------------------
>> | 6 | 4 | 0 |
>> --------------------------------
>>
>>
>> Can anyone help with this please? I have to use DB2 and cannot do any
>> table changes. I have been at this for a couple days and can't seem to
>> get the results I need.
>>

thank you very much everyone. i will give this a shot!!! your help is
most appreciated..and not it is not a classroom exercise.

also, thank you for the explanations too, as this will help me with my
thought process with future queries.

Some ideas:
1)
-------------------- Commands Entered ------------------------------
SELECT RGN.region AS "Region"
, T2T.reason AS "Reason"
, COALESCE(AT2.Reason_Count, 0) AS "Reason Count"
FROM (SELECT DISTINCT region
FROM ALIGNMENT
) RGN
INNER JOIN
TABLE2_TXT T2T
ON 0=0
LEFT OUTER JOIN
(SELECT A.region, T2.reason
, COUNT(*) AS Reason_Count
FROM ALIGNMENT A
INNER JOIN
TABLE2 T2
ON A.store = T2.store
GROUP BY
A.region, T2.reason
) AT2
ON AT2.region = RGN.region
AND AT2.reason = T2T.reason
ORDER BY
"Region", "Reason";
--------------------------------------------------------------------

Region Reason Reason Count
------ ------ ------------
6 1 2
6 2 0
6 3 2
6 4 0

4 record(s) selected.


2)
------------------- Commands Entered ------------------------------
SELECT AT2.region AS "Region"
, T2T.reason AS "Reason"
, MAX(AT2.Reason_Count) AS "Reason Count"
FROM
TABLE2_TXT T2T
LEFT OUTER JOIN
(SELECT A.region, T2.reason
, CASE
WHEN GROUPING(T2.reason) = 0 THEN
COUNT(*)
ELSE 0
END AS Reason_Count
FROM ALIGNMENT A
LEFT OUTER JOIN
TABLE2 T2
ON T2.store = A.store
GROUP BY GROUPING SETS
( (A.region, T2.reason), (A.region) )
) AT2
ON T2T.reason = AT2.reason
OR AT2.Reason_Count = 0
GROUP BY
AT2.region, T2T.reason
ORDER BY
"Region", "Reason";
--------------------------------------------------------------------

Region Reason Reason Count
------ ------ ------------
6 1 2
6 2 0
6 3 2
6 4 0

4 record(s) selected.


3)
-------------------- Commands Entered ------------------------------
SELECT A.region AS "Region"
, T2T.reason AS "Reason"
, SUM(CASE
WHEN T2.reason = T2T.reason THEN
1
ELSE 0
END
) AS "Reason Count"
FROM TABLE2_TXT T2T
LEFT OUTER JOIN
ALIGNMENT A
ON 0=0
LEFT OUTER JOIN
TABLE2 T2
ON T2.store = A.store
GROUP BY
A.region, T2T.reason
ORDER BY
"Region", "Reason";
--------------------------------------------------------------------

Region Reason Reason Count
------ ------ ------------
6 1 2
6 2 0
6 3 2
6 4 0

4 record(s) selected.