CODEWITHSUNDEEP
sql
abetitou
abetitou

Reputation: 3

SQL - COUNT DISTINCT

I need to create a query to do the next, with data with same CLIENT, DATE and STATUS, to select the maximum date of execution and count it. I think with an example it will be easier to undestand.

From this table for example,

Client        Status         Date             Date Execution        
-------       ----------     ------           ---------------------        
AB            100            22/07/2014       23/07/2014 21:35
AB            100            22/07/2014       23/07/2014 05:00
AB            100            22/07/2014       23/07/2014 19:32
AB            100            23/07/2014       24/07/2014 04:15
AB            100            23/07/2014       24/07/2014 20:19
BC            400            24/07/2014       25/07/2014 20:56
BC            400            24/07/2014       25/07/2014 03:36
BC            400            24/07/2014       25/07/2014 17:58
BC            400            24/07/2014       25/07/2014 14:51

I would like to obtain,

Client        Status         Date              Date Execution          COUNT(Distint)
-------    ----------      ------          ---------------------     ----------------------
AB             100        22/07/2014       23/07/2014 21:35             3
AB             100        23/07/2014       24/07/2014 20:19             2
BC             400        24/07/2014       25/07/2014 20:56             4

For the moment I did this query to do find the max date execution, but I dont know how to do for the COUNT (DISTINCT). 

SELECT ip.CLIENT, cps.DATE, cpst.STATUS, cpst.DATE_EXE

FROM IP ip, CPS cps, CPST cpst INNER JOIN (SELECT PF_GUID, MAX(DATE_EXE)
                                          FROM CTR_PORTFOLIOS_TRG 
                                          GROUP BY PF_GUID) cpst2 ON cpst.PF_GUID=cpst2.PF_GUID

WHERE ip.PF_ID = cps.PF_ID AND cps.PF_GUID = cpst.PF_GUID
        AND cpst.STATUS IN (100,400,550)
       AND cps.DATE BETWEEN TO_DATE('01/07/2014', 'DD/MM/YYYY') AND TO_DATE('30/09/2014', 'DD/MM/YYYY')

ORDER BY CLIENT, STATUS, DATE

Any help please to complete the query? Thank you very much

Upvotes: 0

Views: 138

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If I understand correctly, this is a basic aggregation query. I think you might have confused yourself:

SELECT ip.CLIENT, cpst.STATUS, MIN(cps.date), MAX(cpst.DATE_EXE), COUNT(*)
FROM IP ip JOIN
     CPS cps
     ON ip.PF_ID = cps.PF_ID JOIN
     CPST cpst
     ON cps.PF_GUID = cpst.PF_GUID 
WHERE cpst.STATUS IN (100,400,550) AND
      cps.DATE BETWEEN DATE '2014-07-01' and DATE '2014-09-30'
GROUP BY ip.CLIENT, cps.STATUS;

Notes:

  • Replaced the implicit joins with explicit joins. Simple rule: Never use commas in the from clause.
  • Removed the aggregation subquery.
  • Changed the format to use the Oracle DATE identifier for a constant.
  • Use MAX() to get the most recent date/time.
  • Use COUNT(*) to get the count. I'm not sure what you mean by distinct, but for the sample results, count(*) is sufficient.

Upvotes: 1

Lamak
Lamak

Reputation: 70638

SELECT  Client,
        [Status],
        [Date],
        MAX([Date Execution]) [Date Execution],
        COUNT(*) [Count(Distinct)]
FROM YourTable
GROUP BY Client,
         [Status],
         [Date]

Upvotes: 1

Related Questions