Results 1 to 8 of 8
  1. #1
    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. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    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. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    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.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    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. #6
    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. #7
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by vaniupadhyay View Post
    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.
    So, instead of
    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. #8
    Join Date
    Mar 2012
    Posts
    4
    thank you, will try using case on oracle.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •