3

I have a very complicated mySQL query I want to run, and I am having trouble thinking of how to write it.

Basically I have three tables:

a(aID, name);
b(bID, val);
ab(aID, bID, include)

What I want to do is create a query that creates a table like this:

c(aID, percentage);

I want percentage to be (total/possible) in this way. Total is calculated like this. Everytime an a row and b row have a relation AND include is 1 I want total to add the value of b.val. Possible will be the sum of b.val except where ab.include = 0

For example:

a:
+-----+
| aID |
+-----|
| 1   |
| 2   |
| 3   |
+-----+

b:
+-----+-----+
| bID | val |
+-----+-----+
| 1   |   1 |
| 2   |   1 |
| 3   |   1 |
+-----+-----+

ab:
+-----+-----+---------+
| aID | bID | include |
+-----+-----+---------+
| 1   |   1 |       1 |
| 1   |   3 |       0 |
| 2   |   1 |       1 |
| 3   |   1 |       1 |
| 3   |   2 |       1 |
| 3   |   3 |       1 |
+-----+-----+---------+

c:
+-----+------------+
| aID | percentage |
+-----+------------+
| 1   |         50 |
| 2   |      33.33 |
| 3   |        100 |
+-----+------------+

Currently I have this:

SELECT a.aID, (SUM(b.val)/(SELECT SUM(val) FROM b))*100 AS percentage
FROM a, ab, b
WHERE a.aID = ab.aID AND ab.bID = b.bID
GROUP BY a.aID

This gives the percentage aID is paired with bID with respect to weight, but I wanted to add this other calculation.

Is this possible?

Tried this but it didn't work:

I was about to try this:

SELECT a.aID, (SUM(b.val)/(SELECT SUM(val) FROM b,ab WHERE ab.include = 1))*100 AS percentage
FROM a, ab, b
WHERE a.aID = ab.aID AND ab.bID = b.bID AND ab.include = 1
GROUP BY a.aID
dspiegs
  • 548
  • 2
  • 9
  • 24
  • my desired output is seen in c – dspiegs May 01 '13 at 07:28
  • If you could give some context to your question (like telling us what "a" actually is) then it makes it easier to understand your problem (for me at least). – Kevin Brydon May 01 '13 at 07:30
  • 2
    Ok so: A is a person B is an assignment that carries a certain weight (pass/fail) An entry in AB with include at 1 means the person completed the assignment, no entry means they didnt, and an entry with AB with include set to 0 means they were not excused. – dspiegs May 01 '13 at 07:32
  • Why is `aID=2`'s percentage `33.33`? – maroux May 01 '13 at 07:33
  • 1 50% I THINK IT SHOULD B 66.33..WHATS THE LOGIC TO CALCULATE % – Amit Singh May 01 '13 at 07:34
  • Because 2 had only 1 out of three possible b relations. – dspiegs May 01 '13 at 07:34
  • I googled "my sql running sum" and got several valid results, including http://stackoverflow.com/questions/8359077/mysql-running-total-with-count and this one that is marked as duplicate: http://stackoverflow.com/questions/12077575/mysql-aggregate-by-month-with-running-total – Edwin May 01 '13 at 07:43

1 Answers1

4

Try this (sqlfiddle):

SELECT ab.aID, SUM(b.val*ab.include)/(possible.possible-SUM((1-ab.include)*b.val))*100 AS percentage
FROM ab, b, (SELECT SUM(val) possible FROM b) possible
WHERE ab.bID = b.bID
GROUP BY ab.aID

numerator (total): Sum all b values where we have a relation

denominator (possible): From sum of all b values deduct values where there is a relation AND include is 0.

maroux
  • 3,764
  • 3
  • 23
  • 32