# Thread: Problem in formulating query to retrieve data

1. Registered User
Join Date
Mar 2012
Posts
4

## Unanswered: Problem in formulating query to retrieve data

Hi,

I am having problems with formulating a query for retrieving some data.
I have two sample tables as below:

1. testcycletable - with columns: testid and teststatus
testid (FK) contains testcase id eg: 1,2,3...
teststatus contains test case status eg: pass, pass, fail...

2. testtable - with columns: testid and stp
testid (PK) contains testcase id eg: 1,2,3...
stp contains any value between 0.00 to 9.99

The result I want has to be in the following format:

stp | passed | failed | total | total*stp

Here, stp column should be grouped by stp i.e for eg:
the first row will return stp = 2 in stp column, count of testcases with status = pass and stp=2 in "passed" column, count of testcases with status = fail and stp=2 in "failed" column, total no of testcases with stp=2 in "total" column and stp value multiplied by total number of testcases in "total*stp" column.

I can provide some sample values as follows if that may help:

testid teststatus stp
1 passed 2
2 failed 3
3 passed 5
4 passed 2
5 failed 5
6 passed 2
7 failed 4
8 failed 5
9 failed 4
10 passed 3

Any help is deeply appreciated.
Thanks.

2. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
We speak SQL.
Do you speak SQL?
If so, the post actual CREATE TABLE & INSERT statements so we can have same tables & data as you.

3. Registered User
Join Date
Mar 2007
Posts
627
From short look, it seems like ordinary pivot (cross-tab, rows to columns, rotating data, ...) transformation.
Please, search for that feature in your reporting tool (whatever it is).

If you plan to use simple SQL, just use the pivoting method available in your Oracle version:
http://www.oracle-base.com/articles/...tors_11gR1.php
http://www.orafaq.com/wiki/PIVOT
https://forums.oracle.com/forums/thr...174552#9360005
As you did not post any code, I will not post any code too as there are many sample ones in articles I posted.

4. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713
Besides, it would be better if you do the homework yourself and if you do get into trouble, perhaps some "angel" from this forum will help you out.

5. Registered User
Join Date
Mar 2012
Posts
4
anacedent:

Please see sample dataset as expected result as below:

CREATE TABLE `testtable` (
`testid` int(11) NOT NULL,
`stp` int(11) NOT NULL,
PRIMARY KEY (`testid`)
);

INSERT INTO `testtable` (`testid`, `stp`) VALUES
(1, 2),
(2, 3),
(3, 5),
(4, 2),
(5, 5),
(6, 2),
(7, 4),
(8, 5),
(9, 4),
(10, 3),
(11, 5),
(12, 2),
(13, 3),
(14, 4),
(15, 2),
(16, 4),
(17, 5),
(18, 3),
(19, 2),
(20, 5);

CREATE TABLE `testcycletable` (
`testid` int(11) NOT NULL,
`teststatus` varchar(20) NOT NULL,
KEY `testid` (`testid`)
);

INSERT INTO `testcycletable` (`testid`, `teststatus`) VALUES
(1, 'passed'),
(2, 'failed'),
(3, 'passed'),
(4, 'passed'),
(5, 'failed'),
(6, 'passed'),
(7, 'failed'),
(8, 'failed'),
(9, 'failed'),
(10, 'passed'),
(11, 'passed'),
(12, 'failed'),
(13, 'passed'),
(14, 'failed'),
(15, 'failed'),
(16, 'passed'),
(17, 'failed'),
(18, 'passed'),
(19, 'failed'),
(20, 'failed');

Expected Resultset:

utp total passed failed Multiply
2 6 3 3 12
3 4 3 1 12
4 4 1 3 16
5 6 2 4 30

Thanks.

6. Registered User
Join Date
Mar 2012
Posts
4
I tested the following query which worked in MySQL, will this work in case of oracle too?

SELECT x.stp
, COUNT(y.testid) total
, SUM(y.teststatus='passed') passed
, SUM(y.teststatus='failed') failed
, x.stp*COUNT(y.testid) total_x_stp
FROM testtable x
JOIN testcycletable y
ON y.testid = x.testid
GROUP
BY stp;

7. Registered User
Join Date
Mar 2007
Posts
627
I tested the following query which worked in MySQL, will this work in case of oracle too?
No, but you are close. As Oracle SQL does not support BOOLEAN data type, you have to construct expression of supported data type based on that condition.
Code:
`SUM(y.teststatus='passed') passed`
you should use e.g.
Code:
`SUM(case when y.teststatus='passed' then 1 else 0 end) passed`
or
Code:
`COUNT(case when y.teststatus='passed' then 1 end) passed`
or use DECODE instead of CASE (if you like it more).

8. Registered User
Join Date
Mar 2012
Posts
4
thank you, will try using case on oracle.