Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
sql count for a date column
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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_da
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_da
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_da
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_da
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
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
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
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 = ...
>>My final report should look something like what I listed below<<
Can you post which attributes belong to which table?
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(SYSDA
funclib.get_custody_type(l
ROUND(sysdate - Ipe.primary_start_date) No_of_Pmry_Dt,
st.service_desc,
funclib.get_emp_for_p(p.pe
funclib.get_emp_for_p(p.pe
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
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(SYSDA
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
SELECT DISTINCT
p.last_name || ', ' || p.first_name name,
p.person_id,
ROUND(MONTHS_BETWEEN(SYSDA
funclib.get_custody_type(l
ROUND(sysdate - Ipe.primary_start_date) No_of_Pmry_Dt,
st.service_desc,
funclib.get_emp_for_p(p.pe
funclib.get_emp_for_p(p.pe
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
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(SYSDA
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?
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_da
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;
...,
COUNT(lpe.primary_start_da
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
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
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial