[SQL] Percentage Count in een JOIN

[Verwijderd]

Al een tijdje probeer ik de volgende situatie voor elkaar te krijgen, d.m.v. een SQL-query, maar tot nu toe is het me nog niet gelukt....

Hieronder de tabellen:

naam: wf_assignment

code:

id    project_id
--    ----------
11    1
12    1
13    1
14    2

naam: wf_assignment_procedure

code:

id    assignment_id   set_bool
--    -------------   --------
1     11              true
2     11              true

3     12              true
4     12              false
5     12          true

6     13              false
7     13              false

8     14              true
9     14              false

bij elk record in tabel 'wf_assignment' wil ik een veld toegevoegd hebben waarin de percentage staat van de afgehandelde 'wf_assignment_procedure's. Dus het moet er uiteindelijk zo uitkomen:

code:

id    project_id    done_perc
--    ----------    ---------
11    1          100
12    1             67
13    1             0
14    2             50

Weet iemand hier een oplossing voor?

Ik gebruik trouwen MySQL v4.1

[ Voor 4% gewijzigd door Verwijderd op 08-02-2006 17:12 ]

[Verwijderd]

Niet zo 1 2 3 (lijkt me nie theel lastig trouwens) (heb nu geen tijd) maar moet het bij 12 geen 66.6 (67) % zijn?

[Verwijderd]

Ik weet niet welke DB je gebruikt, maar ik ga even uit van een redelijk standaard DB, die ook views aankan.

2 queries maken :

code:

  create view work_total as 
    ( 
    select 
      assignment_id,
      count (*) as total 
    from 
      wf_assignment_procedure 
    group by assignment_id )

code:

  create view work_done as
    ( 
    select 
      assignment_id, 
      count(*) as done 
    from 
      wf_assignment_procedure 
    where 
      set_bool 
    group by 
      assignment_id )

Daarna kun je met de volgende SQL je percentages krijgen. Rest van de velden mag je zelf doen.

code:

Select 
  work_total.assignment_id,
  (( done - total) / total) * 100 as percentage 
from
  work_total, 
  work_done
where
  work_total.assignment_id = work_done.assignment_id

[curry684]

Waarom moeilijk doen met een view?

SQL:

create table #wf_assignment (
    [id] int primary key,
    [project_id] int);
create table #wf_assignment_procedure (
    [assignment_id] int foreign key references #wf_assignment,
    [set_bool] bit);
insert #wf_assignment values (11,1);
insert #wf_assignment values (12,1);
insert #wf_assignment values (13,1);
insert #wf_assignment values (14,2);
insert #wf_assignment_procedure values (11,1);
insert #wf_assignment_procedure values (11,1);
insert #wf_assignment_procedure values (12,1);
insert #wf_assignment_procedure values (12,0);
insert #wf_assignment_procedure values (12,1);
insert #wf_assignment_procedure values (13,0);
insert #wf_assignment_procedure values (13,0);
insert #wf_assignment_procedure values (14,1);
insert #wf_assignment_procedure values (14,0);

select * from #wf_assignment;
select * from #wf_assignment_procedure;

select [id], [project_id], 
    cast(sum(case set_bool when 0 then 0 when 1 then 1 end) * 100 as float) / count(assignment_id)
from #wf_assignment 
join #wf_assignment_procedure on assignment_id = [id]
group by [id], project_id;

drop table #wf_assignment_procedure;
drop table #wf_assignment;

Tested to work in SQL Server 2000, maar MySQL moet dit ook slikken met kleine aanpassingen (en die heeft geen views).

[Jorick]

SQL:

SELECT p.assignment_id,((SELECT count(*) FROM wf_assignment_procedure WHERE assignment_id = p.assignment_id and set_bool = 1) / count(p.assignment_id) * 100) as resultaat 
FROM wf_assignment as a, wf_assignment_procedure as p 
WHERE a.id = p.assignment_id 
GROUP BY p.assignment_id

/edit
shit te laat

[ Voor 11% gewijzigd door Jorick op 08-02-2006 17:26 ]

[Verwijderd]

Bedankt voor de snelle en goede reacties allemaal!

Ik heb de query van 'curry684' een beetje aangepast en de query werkt wel, maar deze query laat niet het record zien in wf_assignment als er geen wf_assignment_procedure's aanwezig zijn voor deze wf_assignment. Dit komt natuurlijk door de 'id' vergelijking. Maar hoe kan ik dit voorkomen?

SQL:

SELECT *,
  IF (COUNT(wf_assignment_procedure.assignment_id),
     CAST((SUM(CASE wf_assignment_procedure.set_bool WHEN 'false' THEN 0 WHEN 'true' THEN 1 END) * 100) AS UNSIGNED) /
                    COUNT(wf_assignment_procedure.assignment_id),
     0) AS perc_done
FROM wf_assignment
JOIN wf_assignment_procedure on assignment_id = wf_assignment.id
WHERE wf_assignment.project_id = 1
GROUP BY wf_assignment.id
ORDER BY date_time DESC;

edit:
join toegevoegd aan sql

[ Voor 14% gewijzigd door Verwijderd op 09-02-2006 13:07 ]

[curry684]

Waarom heb je in godesnaam de cleane moderne join-syntax vervangen door deprecated legacy syntax?

[Verwijderd]

curry684 schreef op donderdag 09 februari 2006 @ 12:42:
Waarom heb je in godesnaam de cleane moderne join-syntax vervangen door deprecated legacy syntax?

Ik zal wel gek zijn, maar ik zie niet echt een verschil... De JOIN is toch precies hetzelfde?

/Edit: die heeft ie dus toegevoegd... laat maar.

Ik heb de query van 'curry684' een beetje aangepast en de query werkt wel, maar deze query laat niet het record zien in wf_assignment als er geen wf_assignment_procedure's aanwezig zijn voor deze wf_assignment. Dit komt natuurlijk door de 'id' vergelijking. Maar hoe kan ik dit voorkomen?

Maak er dan even een LEFT JOIN van ....

[ Voor 41% gewijzigd door Verwijderd op 09-02-2006 13:11 ]

[Verwijderd]

Ok, dat is het dus. Nu krijg ik deze overige records ook te zien.
Bedankt voor de hulp!!!