By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,278 Members | 1,555 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,278 IT Pros & Developers. It's quick & easy.

Assistance with DB2 Query

P: n/a
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.

Sep 22 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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;

Sep 22 '06 #2

P: n/a
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
sh********@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.
Sep 22 '06 #3

P: n/a
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
sh********@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.
Sep 22 '06 #4

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

Sep 22 '06 #5

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

Sep 22 '06 #6

P: n/a
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.

Sep 23 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.