sql count for a date column

You say the count is 3 but there are two different dates.  Shouldn't the count be 2 and 1 if grouped by date?

Dates in Oracle have a time portion built in.  The default display value is DD-MON-YY.

Assuming you want the counts by date try this:
select p.person_id, trunc(Ipe.primary_start_date), count(*)
from person p, legal_primary_episode lpe
where p.person_id = ipe.person_id
and p.person_id = 8900923
group by p.person_id, trunc(Ipe.primary_start_date)

I am not at work anymore I will have to try it tomorrow. There will be multiple different dates for one  person_id. I need a count for all the dates for one person_id. One person_id could have 50 difference dates. And I have several more fields but I didn't list them all because I am going to incorpate this code with minor changes into a larger script.

Thank you in advance

>>I need a count for all the dates for one person_id

Then that should look like:
select p.person_id, count(*)
from person p, legal_primary_episode lpe
where p.person_id = ipe.person_id
and p.person_id = 8900923
group by p.person_id

My final report should look something like what I listed below. I am only having problems with how to display the count for Ipe.primary_start_date, which is the No_of_Pmry_Dt.

Name            Person_id     Age       Legal_Status     No_Pmry_Dt  Service_Desc  Worker
Jim Brow       7894525       2           Temp_Cust              3              Level 1            Mary Jones
Tom Martin   4562561      12          BBLP                       20            Level 2             Jo Smith
Tina Fay         1254887      8            OUHT                      10             Level 3           Sam Terry
Fred Peter      2558794     5             WEDF                      8               Level 5           John Tomas

Use the select as shown by slightwv in http:#a38711703 as "temporary table":

select Name, Person_Id, Age, Legal_Status, No_Pmry_Dt, Service_desc, Worker
from «put your other tables here»
join (select p.person_id, count(*) No_Pmry_Dt
from person p, legal_primary_episode lpe
where p.person_id = ipe.person_id
group by p.person_id) cnt on cnt.person_id = ...

Select all Open in new window

>>My final report should look something like what I listed below<<
Can you post which attributes belong to which table?

ok here is my script

SELECT DISTINCT
       p.last_name || ', ' || p.first_name name,
       p.person_id,
       ROUND(MONTHS_BETWEEN(SYSDATE,p.birth_date)/12,1) age,
       funclib.get_custody_type(lp.person_id) Legal_status,
       ROUND(sysdate - Ipe.primary_start_date) No_of_Pmry_Dt,
       st.service_desc,
       funclib.get_emp_for_p(p.person_id, 'WORKER', 'NAME') worker,
       funclib.get_emp_for_p(p.person_id, 'SUPERVISOR', 'NAME', SYSDATE, 'P') supervisor
       FROM sac_legal_bas lb,
       sac_legal_stats_info lsi,
       sac_legal_primary_episode lce,
       sac_legal_part lp,
       sac_f_agency_link cal,
       sac_person p,
       sac_case_part cp,
       sac_prov_name pn,
       sac_ref_data rd,
       sac_ref_data rdc,
       sac_placemnt_settn ps,
       sac_service_type st
   WHERE rdc.domain_code = 'LegalActionTypes'
   AND rdc.parent_ref_data_code = lb.legal_action_code
   AND rdc.ref_data_code = lb.legal_action_type_code
   AND lb.legal_action_type_code NOT IN ('PC')
   AND lb.legal_action_code = 'AGENCYLGLSTATUS'
   AND lb.legal_bas_id = lsi.legal_bas_id
   AND lsi.primary_id = lpe.primary_id
   AND lsi.termination_date IS NULL
   AND lpe.primary_start_date IS NOT NULL
   AND lsi.created_in_error_flag = 0
   AND lsi.legal_bas_id = lp.legal_bas_id
   AND funclib.get_persons_fam(lp.person_id) = cal.case_id
   AND cal.end_eff_date IS NULL
   AND cal.local_agency_id = 10022
   AND lp.person_id = p.person_id
   AND p.person_id = cp.person_id
   AND cp.current_status_code = 'ACTIVE'
   and ROUND(MONTHS_BETWEEN(SYSDATE,p.birth_date)/12,1) between 11 and  14
   AND pn.end_date(+) IS NULL
   AND pn.provider_id(+) = ps.service_provider_id
   AND rd.domain_code(+) = 'CPPlamntType'
   AND rd.ref_data_code(+) = ps.plmnt_type_code
   AND ps.child_id(+) = lp.person_id
   AND ps.service_id = st.service_id
   AND st.service_desc NOT IN ('Kinsh Care - Relative Hm')
   AND NVL(ps.end_reason_code, 'x') <> 'CREATEDINERROR'
   AND ps.end_date(+) IS NULL
   ORDER BY 1, 2, 3

So all I need is to figure out how to get a count for No_of_Pmry_Dt.

name          person_id  age legal_status  no_pmry_Dt  service_desc   worker        supervisor
Jim Brow     7894525    2   Temp_Cust      3                Level 1            Mary Jones   Dan Jones
Tom Martin 562561      12  BBLP              20             Level 2             Jo Smith      Annie Pere
Tina Fay      1254887     8   OUHT             10             Level 3           Sam Terry     Val Smith
Fred Peter  2558794     5   WEDF              8               Level 5           John Tomas  John Matt

You are showing these lines -
ROUND(sysdate - Ipe.primary_start_date) No_of_Pmry_Dt
sac_legal_primary_episode lce,
AND lsi.primary_id = lpe.primary_id

I assume you meant lpe in all cases). If so, can you post the relevant data for that table (i.e. all the records that contain those person_ids in your example)?
Also, your output shows no_pmry_dt (I also assume you mean no_of_pmry_dt) of 20 for Tom Martin. Does that represent the rounded number of days between the start_date and sysdate? Are you looking for an additional column to show the count of the primary_start_dates?

No, "ROUND(sysdate - Ipe.primarty_start_date) No_of_Pmry_Dt" was suppose to be commented out. It was suppose to return the count for No_of_Pmry_Dt. But it did not return the count. Example: Jim Brow no_pmry_Dt(3), and Tom Martin (20), and Tina Fay (10). I need to retrieve the count. Please review my first txt. It has 3 no_pmry_Dt, so the count would be 3 for this person_id.

Try sustituting this
...,
COUNT(lpe.primary_start_date) OVER (PARTITION BY p.person_id ORDER BY lpe.primary_start_date) No_of_Pmry_Dt,
ROW_NUMBER() OVER (PARTITION BY p.person_id ORDER BY lpe.primary_start_date) rn,
...
for this -
...,
 ROUND(sysdate - Ipe.primary_start_date) No_of_Pmry_Dt,
...
Then SELECT <columnlist> FROM
<wrap around your query modified as above>
WHERE rn = 1;

Awking00 I will try this on Monday. Thank you very much. I am out of the office and will not return til Monday. I am using Oracle sql. Is this code for Oracle/sql? I don't recall a Partition by statement. The round function brought in the wrong result.  
This code below works but how do I incorporate it into my code to make it work.

select distinct p.person_id, count(*) No_of_Pmry_Dt
from person p, sac_legal_primary_episode lpe
where p.person_id = lpe.person_id
---p.person_id = 7400923
group by p.person_id

Returns
person_id     No_of_Pmry_Dt
7400923                3

please make sure you check out the comment of QLemo:
http://www.experts-exchange.com/Database/Oracle/Q_27974896.html#a38712962

I suspect your issue is that the COUNT(*) function currently simply doens't return the correct values, due to the many other joins you have in your query, and this "inline" view will solve that issue.
see also this article about DISTINCT and GROUP BY wihch you use in your queries and the "solutions":
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html

Hi awking00, I ran my code using the partition by and over clause and it did not return the right count. I also ran it alone and it did not return the right count. I think this might be a data issuse. I have contacted our dba regarding a possible data issue. After I speak with her I will sign off on this open question. Are the group by or partition by clause the only clauses I can use to return the count on a group of records?

The only two ways to get an aggregate (e.g. a count) are with a group by or a count() OVER ([PARTITION BY expr] ORDER BY expr). Using group by requires that all non_aggregated columns be include in the group by clause. So if you want the count of things by id from a table such as
id   thing  other
1     x        a
1     y        a
1     z        b
You could use select id, count(*) from table group by id.
But if you also want the other values, you would need to do the window function like -
select id, count(thing) over (partition by id order by id) since the group by function would require you to include the other in the group by clause.

Was the count returned too small or too big? I suspect that it might be too big. Given the extensive number of joins in your query, it's hard to say what causes the distortion. The window function probably requires a different partition by or order clause or both, but with access to the data, I wouldn't know where to begin.

Thanks for all the additional information