0

So, I have 3 tables that I am attempting to get counts for based on a groupid, and a task code. There are a few issues I am having as some of the relationships are many to one, which I think is somehow inflating my counts. I will list my 3 tables with the pertinent attributes.

task_table contains:

task_code - would like to get the counts of each one in a group id, would like to use the latest instance basedon event date.

sol_id -used to join to worktable; many sol_id to one m_id is possible

edate -need to use to get one record

cur_id - where cur_id = 1 in the where clause

worktable contains:

sol_id - used to join to task_table

m_id - used to join to grouptable

grouptable contains:

m_id

groupid- used to group the task_code to get count

I'd like the end result to look like:

group_id task_count task
5555 45 A
5555 4 N
5624 67 A
5624 23 O
5624 42 X

I have been attempting to run a number of queries, but the counts I am getting back do not look correct. I am concerned that it is somehow returning more than one instance of the m_id somehow? Here is the query in question:

select  c.groupid, count(c.groupid) group_count, a.task_code from task_table a
join worktable b
on a.sol_id = b.sol_id
join grouptable c
on b.m_id= c.m_id
where   a.cur_id = 1 and a.task_code is not null 
group by c.groupid, a.task_code;

If I add 'edate = (select max(edate) from task_table)' in the where clause, it returns an empty table.

I am unsure how to incorporate edate to get only the newest record that fits the criteria in the where clause. The reason I think I want to use this is because there could be more than one sol_id that is associated with a m_id, so i'd just like to include only the newest record with a cur_id in the count. Thank you for your time.

sample data

task_table

task_code sol_id edate cur_id
A 23 6/7/09 1
A 24 6/4/09 1
A 23 6/10/09 0
B 45 6/2/09 1
B 42 6/3/09 1
C 34 10/8/10 0
C 83 9/10/09 1

work table

sol_id m_id
23 1234
24 1234
45 1832
42 1343
83 7623

group table

m_id group_id
1234 A76
1832 Y23
1343 A76
7623 Y23

looking at these tables, the result should look like the following

group_id task_count task
A76 2 A
Y23 1 C

( A76 should only count sol_id 23 and 42) ( Y23 should only count sol_id 83)

5
  • Can post some sample data for each table and the schema ? – armitage Nov 24 '20 at 18:32
  • 1
    Post sample data as CREATE TABLE + INSERT INTO scripts (or create online fiddle). – Akina Nov 24 '20 at 20:20
  • explain what are you counting as task_count and why choosing A and C as task for each group_id, your question is not clear – eshirvana Nov 24 '20 at 23:13
  • I'm sorry for the confusion. maybe i can explain it a bit better. what it comes down to is m_id represents a house. Each house only be counted once. groupid is the county it is in. sol_id is the visit to the house. task is what was done during that visit to the house. so in some cases there are multiple visits to each house (pipes burst in first visit, but follow up was for sink installation). cur_id is like an 'importance of visit' field and should equal 1. So what i need is each house to only get counted one time in the county it's in, based on the most recent important visit and task. – user7002207 Nov 25 '20 at 12:35
  • Your group_id's have more than one house (m_id) belonging to them though. Your results look like you only want to capture the visits for one house per county? – Boneist Dec 3 '20 at 8:36
0

mmm based on your sample date your query is not wrong. but your expected results are wrong.

select
    a.task_code,a.sol_id,a.cur_id
    ,b.m_id
    ,c.groupid
FROM
    task_table AS a
    inner join worktable AS b on a.sol_id = b.sol_id
    inner join grouptable AS c on b.m_id= c.m_id
    where
        a.cur_id = 1 and a.task_code is not null
ORDER BY c.groupid, b.sol_id;

select
    c.groupid
    ,count(c.groupid) AS group_count
    ,a.task_code
from task_table AS a
join worktable AS b on a.sol_id = b.sol_id
join grouptable AS c on b.m_id= c.m_id
where
    a.cur_id = 1 and a.task_code is not null 
group by
    c.groupid, a.task_code
;

enter image description here

the group_id A76 is linked to the work table with sol_id 23,24 and 42; 23 and 24 are task_code A. 42 is task_code B. Y23 has two m_id that connect sol_id 45 and 83 to task code A and B. So the second output in the image is as you expect but has 2 more rows related to the task B. Maybe the sample date is wrong because a can't see a wrong result to fix.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.