CODEWITHSUNDEEP
sqlms-access
Jossy
Jossy

Reputation: 1021

Where do I put a WHERE statement?

I have the following two tables of data:

+----------+------+--------+--+---------------+-------+------------+--+--------+--------+-----------------------------+
| stat_atp |      |        |  | tblTourns_atp |       |            |  | Output |        |                             |
+----------+------+--------+--+---------------+-------+------------+--+--------+--------+-----------------------------+
| ID_T     | FS_1 | FSOF_1 |  | ID_T          | ID_Ti | DATE       |  | ID_T   | OUTPUT | Rationale                   |
| 1        | 20   | 40     |  | 1             | 1     | 01/01/2019 |  | 1      | 50%    | ID_T "1"                    |
| 2        | 30   | 100    |  | 2             | 1     | 05/01/2019 |  | 2      | 31%    | ID_T "1" & "2" & "3"        |
| 3        | 40   | 150    |  | 3             | 1     | 03/01/2019 |  | 3      | 32%    | ID_T "1" & "3"              |
| 4        | 30   | 100    |  | 4             | 2     | 04/01/2019 |  | 4      | 30%    | ID_T "4"                    |
| 5        | 30   | 100    |  | 5             | 2     | 05/01/2019 |  | 5      | 30%    | ID_T "4" & "5"              |
| 6        | 40   | 150    |  | 6             | 2     | 06/01/2019 |  | 6      | 29%    | ID_T "4" & "5" & "6"        |
| 7        | 20   | 40     |  | 7             | 3     | 01/01/2019 |  | 7      | 50%    | ID_T "7"                    |
| 8        | 30   | 100    |  | 8             | 3     | 08/01/2019 |  | 8      | 35%    | ID_T "7" & "8" & "9" & "10" |
| 9        | 40   | 150    |  | 9             | 3     | 02/01/2019 |  | 9      | 32%    | ID_T "7" & "9"              |
| 10       | 20   | 40     |  | 10            | 3     | 06/01/2019 |  | 10     | 39%    | ID_T "7" & "9" & "10"       |
+----------+------+--------+--+---------------+-------+------------+--+--------+--------+-----------------------------+

(Rationale is a column to explain the rationale for the OUTPUT column, not a field per se)

I currently have the following SQL that sums gives FS_1 as a % of FSOF_1 by ID_Ti:

from tbltourns_atp t inner join stat_atp s on t.id_t = s.id_t
group by t.id_ti

I'd now like to add that % to every line in the tblTourns_atp based upon the sum of ID_Ti records that are on or before the date for that line. I'm pretty sure I'll need a sub query with a WHERE statement but I can't figure out where and the syntax. Thanks in advance.

Upvotes: 0

Views: 53

Answers (2)

Lee Mac
Lee Mac

Reputation: 16025

You could also solve this using joins in place of a correlated subquery, which may offer some improvement in performance:

select t1.id_t, sum(t3.fs_1)/sum(t3.fsof_1) as output
from
    (
        tbltourns_atp t1 inner join tbltourns_atp t2 on
        t1.id_ti = t2.id_ti and t1.date >= t2.date
    ) 
    inner join stat_atp t3 on t2.id_t = t3.id_t
group by t1.id_t

Upvotes: 1

forpas
forpas

Reputation: 164184

Join the tables and use a correlated subquery for the output:

SELECT t.id_t, t.id_ti, t.Date, 
    (
      select round(100 * sum(ss.fs_1) / sum(ss.fsof_1), 0) 
      from tbltourns_atp as tt inner join stat_atp as ss on tt.id_t = ss.id_t
      where tt.id_ti = t.id_ti and tt.date <= t.date
    ) as output
FROM  tbltourns_atp as t;

Results:

id_t    id_ti   Date        output
1       1       1/1/2019    50
2       1       5/1/2019    31
3       1       3/1/2019    32
4       2       4/1/2019    30
5       2       5/1/2019    30
6       2       6/1/2019    29
7       3       1/1/2019    50
8       3       8/1/2019    33
9       3       2/1/2019    32
10      3       6/1/2019    35

Note that the Output you posted as expected fro id_t 8 and 10 are wrong.

Upvotes: 2

Related Questions