Count/Sum distinct problem

[2eXtreme]

Hey guys, I'm currently testing my application with some scripts I have written. I'm using the following query to list known errors within my application, and the status of the tests that are are affected by each error:

SELECT
ERROR.BG_ERROR_ID,
ERROR.BG_USER_03 as HardwareID,
count (RUN.CY_RUN_ID) as NumAffected,
sum (CASE RUN.RESULT when 'Passed' THEN 1 else 0 end) as Passed,
sum (CASE RUN.RESULT when 'Failed' THEN 1 else 0 end) as Failed,
FROM ERROR
INNER JOIN LINK on ERROR.BG_ERROR_ID = LINK.LN_ERROR_ID
INNER JOIN TESTCYCL on TESTCYCL.TC_TESTCYCL_ID = LINK.LN_ENTITY_ID
INNER JOIN RUN on TESTCYCL.TC_RUN_ID = RUN.CY_RUN_ID
WHERE ERROR.BG_STATUS != 'Closed'
AND LINK.LN_ENTITY_TYPE = 'TESTCYCL'
group by ERROR.BG_ERROR_ID, ERROR.BG_USER_03
order by NumAffected DESC

A run of this query produces something like:

BG_ERROR_ID.....HardwareID.....NumAffected.....Passed.....Failed
----------------------------------------------------------------
286.............87526..........10..............5..........5
226.............82309..........20..............15.........5
233.............17526..........21..............5..........16
586.............23346..........3...............2..........1
555.............87886..........10..............9..........1
200.............27526..........12..............5..........7

As you can see, the number of Passed and Failed combined amounts to the NumAffected. However, what I really need to be doing is a distinct count on NumAffected, i.e.:

SELECT
ERROR.BG_ERROR_ID,
ERROR.BG_USER_03 as HardwareID,
count (distinct RUN.CY_RUN_ID) as NumAffected,
sum (CASE RUN.RESULT when 'Passed' THEN 1 else 0 end) as Passed,
sum (CASE RUN.RESULT when 'Failed' THEN 1 else 0 end) as Failed,
FROM ERROR
INNER JOIN LINK on ERROR.BG_ERROR_ID = LINK.LN_ERROR_ID
INNER JOIN TESTCYCL on TESTCYCL.TC_TESTCYCL_ID = LINK.LN_ENTITY_ID
INNER JOIN RUN on TESTCYCL.TC_RUN_ID = RUN.CY_RUN_ID
WHERE ERROR.BG_STATUS != 'Closed'
AND LINK.LN_ENTITY_TYPE = 'TESTCYCL'
group by ERROR.BG_ERROR_ID, ERROR.BG_USER_03
order by NumAffected DESC

A run of this query produces something like:

BG_ERROR_ID.....HardwareID.....NumAffected.....Passed.....Failed
----------------------------------------------------------------
286.............87526..........7...............5..........5
226.............82309..........13..............15.........5
233.............17526..........10..............5..........16
586.............23346..........2...............2..........1
555.............87886..........4...............9..........1
200.............27526..........10..............5..........7

As you can see, the number of NumAffected has now dropped, and Passed and Failed no longer amount to NumAffected. What can I change in my query to make Passed and Failed amount to distinct NumAffected?

If I try to do a distinct sum on Passed and Failed, e.g.:

SELECT
ERROR.BG_ERROR_ID,
ERROR.BG_USER_03 as HardwareID,
count (distinct RUN.CY_RUN_ID) as NumAffected,
sum (distinct CASE RUN.RESULT when 'Passed' THEN 1 else 0 end) as Passed,
sum (distinct CASE RUN.RESULT when 'Failed' THEN 1 else 0 end) as Failed,
FROM ERROR
INNER JOIN LINK on ERROR.BG_ERROR_ID = LINK.LN_ERROR_ID
INNER JOIN TESTCYCL on TESTCYCL.TC_TESTCYCL_ID = LINK.LN_ENTITY_ID
INNER JOIN RUN on TESTCYCL.TC_RUN_ID = RUN.CY_RUN_ID
WHERE ERROR.BG_STATUS != 'Closed'
AND LINK.LN_ENTITY_TYPE = 'TESTCYCL'
group by ERROR.BG_ERROR_ID, ERROR.BG_USER_03
order by NumAffected DESC

I get something like:

BG_ERROR_ID.....HardwareID.....NumAffected.....Passed.....Failed
----------------------------------------------------------------
286.............87526..........7...............1..........1
226.............82309..........13..............1..........1
233.............17526..........10..............1..........1
586.............23346..........2...............1..........1
555.............87886..........4...............1..........1
200.............27526..........10..............1..........1

Please help, it's very much appreciated!

Thanks

[2eXtreme]

Can someone please help me?

[2eXtreme]

Can someone tell me if what I want is even possible?

Thanks

[2eXtreme]

Can anyone help me please?

If it helps, I was thinking, I'd just have to strip the table down by listing distinct RUN.CY.RUN_ID's , and from the new table containing only distinct RUN.CY.RUN_ID's , I'd count Passed/Failed etc. The thing is, I'm not allowed to create tables, so I'd be using subselects...

Please help, it really is very important

[tesuji]

Hi 2eXtreme,

in another thread http://www.daniweb.com/forums/thread127644.html there was a similar
problem like yours. I gave the advice to solve it with the new with clause. You should carefully compare the table structure and given solution in post #7 with your table structure. I think it is possible to adopt the there given solution to your problem successfully. (#5 is similar too but not that useful as #7, I think so.)

That solution works fine for MySQL, yet I have used only standard SQL features, so running such a with-clause select statement on MS SQLServer would be no problem.

If I had time I would have rewritten the select of #7 to meet your requirement, maybe by end of this week I will get more time.

Suggestion: pls post your complete table structure, e.g. create table statement and give a sample of some table data also of the result you expected (maybe also some INSERT statements to create a testing environment on my database).

krs,
tesu

[2eXtreme]

Hey tesu,

thanks very much for your reply, I really appreciate your time! :D

How would I go about applying post #7 to my problem? Would I need to use the with statement to create a temporary table? If so, what would it contain? A column for bug id, a column for passed and failed etc?

Thanks very much!

[tesuji]

Your problem is kind of running totals just like that of #7. The examples you have shown to date contain consolidated data only. So I can't figure out the relations between NumAffected, Passed, Failed etc. Maybe it s a good idea to post some original data and its consolidated data to figure this out.

tesu

[2eXtreme]

Here is a brief overview of the table structure, and some rows of some columns for each table in use:

ERROR____________LINK_______________TESTCYCL________RUN
BG_ERROR_ID ------>LN_ERROR_ID
__________________LN_ENTITY_ID------->TC_TESTCYCL_ID
____________________________________TC_RUN_ID--------->CY_RUN_ID

ERROR
------------------------------------------
BG_ERROR_ID-- BG_STATUS-- BG_USER_03
5__ Open__ 58778
6__ Closed__ 21122
7__ Closed__ 78944
8__ Open__ 74523
9__ Closed__ 63251
... ... ...

LINK
------------------------------------------
LN_ERROR_ID-- LN_ENTITY_ID-- LN_ENTITY_TYPE
5__ 3971__ TESTCYCL
6__ 3971__ TESTCYCL
7__ 3971__ TESTCYCL
... ... ...

TESTCYCL
--------------------------
TC_TESTCYCL_ID-- TC_RUN_ID
3971__ 119__
3972__ 119__
3973__ 119__
... ...

RUN
--------------------------
CY_RUN_ID-- RESULT
0__ Failed
119__ Passed
147__ Passed
204__ Failed
... ...

I want a query that produces results so that passed and failed add up to the distinct num affected, so something like:

BG_ERROR_ID-- HardwareID-- NumAffected-- Passed-- Failed
----------------------------------------------------------------
5__ 58778__ 7__ 2__ 5
6__ 21122__ 13__ 10__ 3
7__ 78944__ 10__ 9__ 1

Sorry about the poor formatting of this post, I tried to lay it out to make it easier to read...

Thanks very much for your help!

[2eXtreme]

has anyone else on this forum had a similar problem in the past?

if i do a distinct count on run ids where status = passed, i get the correct number of passes, and its the same for fails, its just making all 3 distinct set of numbers, i.e. numaffected, passed, and failed all appear in the one table. Is there a way i could run a query for distinct numaffected, distinct passed, and distinct failed, and have each of these as 3 separate columns on my main table? maybe by using a join command?

[tesuji]

Hi again,

...
YOUR LAST POST:
I want a query that produces results so that passed and failed add up to the distinct
num affected, so something like:

BG_ERROR_ID-- HardwareID-- NumAffected-- Passed-- Failed
----------------------------------------------------------------
5__ 58778__ 7__ 2__ 5
6__ 21122__ 13__ 10__ 3
7__ 78944__ 10__ 9__ 1
. . .

YOUR FIRST POST:
A run of this query produces something like:

BG_ERROR_ID.....HardwareID.....NumAffected.....Passed.....Failed
----------------------------------------------------------------
286.............87526..........10..............5..........5
226.............82309..........20..............15.........5
233.............17526..........21..............5..........16
586.............23346..........3...............2..........1
555.............87886..........10..............9..........1
200.............27526..........12..............5..........7

As you can see, the number of Passed and Failed combined amounts to the NumAffected.
However, what I really need to be doing is a distinct count on NumAffected, i.e.:

Well, in your FIRST POST you said that the result wouldn't meet your conception
(see: "However, what I really need to...")

Your LAST POST shows the same result as your FIRST POST, that is passed+failed mounts up to numaffected. Now you state that this last result would be the result you are looking for. Isn't that a true contradiction? (If last result were correct you would have already found the correct solution as given in your first post!)

I think, before I invest some effort in supporting you to solve your problem you should figure out what you actually want to get from your tables.

krs,
tesu

[tesuji]

Addition
---------
Based on your given instances ERROR, TESTCYCL, LINK and RUN from post #8

select * from error join link join testcyl join run

gives:

5    open      58778    3971    test    119    failed
6    closed    21122    3971    test    119    failed
7    closed    78944    3971    test    119    failed

(Duplicate columns dropped. I have simplified your naming,
e.g. test instead of TESTCYCL.)

How to compute 7, 2, 5 and 13, 10, 3 etc from the selected data?
Where is the discrepancy?

[pclfw]

I can see why there is confusion about what you want from your data and precisely what is in the tables.

Possibly a different way of looking at the data could be -

SELECT SOURCE.BG_ERROR_ID,
             SOURCE.BG_USER_03 AS HardwareID,
             count (SOURCE.CY_RUN_ID)     AS NumAffected,
             sum   (SOURCE.Passed)        AS Passed,
             sum   (SOURCE.Failed)        AS Failed,
      FROM (SELECT ERROR.BG_ERROR_ID,
                   ERROR.BG_USER_03       AS HardwareID,
                   DISTINCT RUN.CY_RUN_ID,
                   1                      AS Passed,
                   0                      AS Failed,
            FROM   ERROR
            INNER JOIN LINK ON ERROR.BG_ERROR_ID = LINK.LN_ERROR_ID
            INNER JOIN TESTCYCL ON TESTCYCL.TC_TESTCYCL_ID = LINK.LN_ENTITY_ID
            INNER JOIN RUN ON TESTCYCL.TC_RUN_ID = RUN.CY_RUN_ID
            WHERE ERROR.BG_STATUS != 'Closed'
            AND   LINK.LN_ENTITY_TYPE = 'TESTCYCL'
            AND   RUN.RESULT = 'Passed'
            GROUP BY ERROR.BG_ERROR_ID, ERROR.BG_USER_03
      
            UNION ALL
      
            SELECT ERROR.BG_ERROR_ID,
                   ERROR.BG_USER_03       AS HardwareID,
                   DISTINCT RUN.CY_RUN_ID,
                   0                      AS Passed,
                   1                      AS failed,
            FROM   ERROR
            INNER JOIN LINK ON ERROR.BG_ERROR_ID = LINK.LN_ERROR_ID
            INNER JOIN TESTCYCL ON TESTCYCL.TC_TESTCYCL_ID = LINK.LN_ENTITY_ID
            INNER JOIN RUN ON TESTCYCL.TC_RUN_ID = RUN.CY_RUN_ID
            WHERE ERROR.BG_STATUS != 'Closed'
            AND   LINK.LN_ENTITY_TYPE = 'TESTCYCL'
            AND   RUN.RESULT = 'Failed'
            GROUP BY ERROR.BG_ERROR_ID, ERROR.BG_USER_03) SOURCE
      ORDER BY SOURCE.NumAffected DESC

This code selects all of the PASSED test results and FAILED test results separately, assigning a numeric '1' in the relevant column.

The results of these two SELECTS are UNIONed and passed to the consolidating SELECT. Now the number SHOULD be consistant. If they are not then run the sub-query on its own to see the data being passed into consolidation. This should help you sort out any problems or misunderstandings with your requirements & data.

Please note that this code HAS NOT BEEN TESTED! It has not even SEEN Query Analyser. :)

[petrhorak]

Hello,

I have similar problem. Do you thing there is a different way?

I need to Count Distinct (but small set of data has to be let's say multiplied by 100).

Is is possible to say somehow:

SELECT t1.f1, t2.f2, t3.f3, 
(CASE t4.f4 
WHEN A THEN COUNT(distinct t4.f5)*100 
ELSE Count(distinct t4.f5))
FROM t1 
INNER JOIN ... (including all tables t2,t3,t4)
GROUP by t1.f1, t2.f2, t3.f3

t4.f4 is not in GROUP BY clause
and that is actually message from DB2, tat i cannot use this field as it's not known :(

Do you have any idea how to redo this SQL to work? :)

Thanks a lot in advance.

[petrhorak]

Ah, I just found sollution by creating temporary column, which separated data into 2 groups, then CASE in SELECT was counting for 1 group and summing for second group) after that i encapsulated it all into Derived table and SELECT again without 1 column (the temporary one) with SUM over it (all counts + all sums)...

but still hoping to find easier sollution :D

because the table i had to add temporary column, has now almost million rows :(