Help with duplicate rows due to date/time in query

[lilmisspink07]

Hello,

I am trying to pull a list of sample counts by test. I only want 1 row per test result so that I can build a report in Cognos showing the rate of a particular result as a percentage of the total results.

My problem is that I am getting duplicate rows for the same result - in the example below I have 2 - 5 rows for each result instead of just 1 for each result. I have narrowed this down to being caused by the date - the date part is the same but the time parts are different so multiple rows are being pulled. I tried using trunc() but although the display now shows the same date without the time part, the duplicate rows are still being pulled.

Any help would be much appreciated!!

This is the code for the query:

SELECT DISTINCT
    io.lab_id AS "LAB ID",
    l.name AS "LAB NAME",
    mtr.marketingtest_id AS "TEST ID",
    mtl.name AS "TEST NAME",
    mtr.marketingresult AS "MARKETING RESULT",
    COUNT(DISTINCT mtr.sample_id) AS "NO OF SAMPLES",
    TRUNC(os.statusdate) AS "STATUS DATE",
    os.orderstatuscodes_id AS "ORDER STATUS",
    io.redoorderid AS "REDOORDERID",
    mtl.locale_id AS "LOCALE"
FROM igen_sample s
INNER JOIN igen_igenityorder io ON s.igenityorder_id = io.igenityorder_id
INNER JOIN igen_orderstatus os ON io.igenityorder_id = os.igenityorder_id
INNER JOIN igen_marketingtestresult mtr ON s.sample_id = mtr.sample_id
INNER JOIN igen_lab l ON io.lab_id = l.lab_id
INNER JOIN igen_marketingtestlocale mtl ON mtr.marketingtest_id = mtl.marketingtest_id
 
WHERE mtl.locale_id = 1
AND os.orderstatuscodes_id IN (8, 9)
AND mtr.marketingtest_id = 84
AND os.statusdate BETWEEN '24-OCT-2011' AND '31-OCT-2011'
 
GROUP BY mtr.marketingtest_id, mtl.name, mtr.marketingresult, os.statusdate, os.orderstatuscodes_id, io.lab_id, l.name, io.redoorderid, mtl.locale_id
ORDER BY mtl.name
 

[Rabbit]

You need to group by the truncated date.