user3454116 user3454116 - 3 years ago 186
MySQL Question

Hierarchy in a query to get unique count with semi structured data

I have a table that tracks the Current Work and Status of the work. So in my table column 'Current Work' captures the product name along with the different tasks being done on the product.

If the the Work has sub activities, the column 'Work Parent Name' is filled with the 'Current Work', else it remains blank if the Work is standalone. So the count needs to be taken only once either at the Work level or the sub activities level, hence cannot be used completely in an hierarchical path.

The 'Status' column is updated with the progress of either the work or the sub work.

![Work Table

Now, as the data is not properly structured, i am finding it a bit difficult to count the 'In Progress' status of Products. So if the status of any work or sub activity under the work is in progress, i want to count the Work to be in progress

So the output i am looking for is below :

![![![enter image description here

I tried to do self join but as the sub activities are same, i am getting wrong results. Is their any way i can achieved my result from this type of data-set.

Answer Source

Ok...I am a little confused as to what you are asking. Shouldn't product 5 have in progress of 2 if you are doing a count? Or are you just trying to get a 1 if any are in progress.

Try this...

select w1.work as work, count(*) countInProgress
from work w1 
inner join work w2 on w1.work = w2.parent
where w2.status = 'In Progress'
group by w1.work

or this if you want to include the in progress of the parent as well.

select workItem, count(*) countInProgress from
(
select work, @workItem := if(parent is not null,parent,work) workItem
, status
from work
) t 
where status = 'In Progress'
group by workItem

Either way the question is a little unclear. How does product 2 have a wip task "In Progress" but is not in progress itself?

Build script

CREATE TABLE work (work varchar(20), parent varchar(20), status varchar(20));
INSERT INTO work VALUES
('Product 1',null,'In Progress'),
('Service', 'Product 1','Completed'),
('Delivery', 'Product 1', 'In Progress'),
('Transportation', 'Product 1', 'Completed'),
('Product 2',null,'In Progress'),
('Service', 'Product 2',''),
('Delivery', 'Product 2', 'In Progress'),
('Transportation', 'Product 2', ''),
('Product 3',null,'Completed'),
('Product 4',null,'In Progress'),
('Product 5',null,'In Progress'),
('Delivery', 'Product 5', 'In Progress'),
('Transportation', 'Product 5', 'In Progress')

sqlfiddle link if you want to play with it... http://sqlfiddle.com/#!9/e0fa02/15 Hope this helps...

EDIT AFTER COMMENT:

select w1.work as work
,if(w1.status = 'In Progress' or w2.status = 'In Progress',1,0) countInProgress
from work w1 
left join work w2 on w1.work = w2.parent
where w1.parent is null
group by w1.work

Give this a whirl...

Also, if you want to see where all of the sub items are marked as In Progress

select w1.work as work
,if(w1.status = 'In Progress' or w2.status = 'In Progress',1,0) countInProgress
,@allInProgress := case when @allInProgress is null and w2.status = 'In Progress' then 1
                        when @allInProgress is null and (w2.status <> 'In Progress' or w2.status is null) then 0
                        when w2.status = 'In Progress' then @allInProgress * 1
                        when w2.status <> 'In Progress' then @allInProgress * 0
                        end allInProgress
from work w1 
left join work w2 on w1.work = w2.parent
where w1.parent is null
group by w1.work
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download