sql count for a date column

Posted on 2012-12-20
Last Modified: 2012-12-27
This code from part of a larger program but I just need to fiqure out how to get the count for the date column. For example this one is (3). I tried -- count (Ipe.primary_start_date).

For example:

person_id    primary_start_date
8900923      14-NOV-11
8900923      14-NOV-11
8900923       25-SEP-09

select p.person_id, Ipe.primary_start_date
from person p, legal_primary_episode lpe
where p.person_id = ipe.person_id
and p.person_id = 8900923
Question by:shizue
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
  • +2
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38711592
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)

Author Comment

ID: 38711695
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
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38711703
>>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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users


Author Comment

ID: 38712192
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
LVL 70

Expert Comment

ID: 38712962
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 = ...

Open in new window

LVL 32

Expert Comment

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

Author Comment

ID: 38713378
ok here is my script

       p.last_name || ', ' || p.first_name name,
       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,
       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
LVL 32

Expert Comment

ID: 38713839
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?

Author Comment

ID: 38714017
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.
LVL 32

Expert Comment

ID: 38714111
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;

Author Comment

ID: 38714812
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

person_id     No_of_Pmry_Dt
7400923                3
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38715129
please make sure you check out the comment of QLemo:

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":
LVL 32

Accepted Solution

awking00 earned 500 total points
ID: 38715712
Try the attached script

Author Comment

ID: 38721818
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?
LVL 32

Expert Comment

ID: 38722128
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.

Author Closing Comment

ID: 38724899
Thanks for all the additional information

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question