logo
Tags down

shadow

sql join table to subquery


By : Bruno Brandão
Date : May 06 2020, 07:36 AM

I have the next 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 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.

Answer :

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

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;

expected 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.



Share : facebook icon twitter icon
Related Posts Related Posts :
  • clear array after form submit
  • pass url variable to javascript
  • access data using api
  • div position fixed css
  • add horizontal line between two div
  • disable toggle button css
  • css fixed line height
  • background image not working
  • table cell border
  • text line break css
  • overlay div on div
  • text direction rtl css
  • javascript convert string to an object
  • bind inner div click to outer div
  • jquery ajax oncomplete
  • how to use promise in for loop
  • jquery get element using variable
  • javascript send textbox value
  • pass div class content to another class or id
  • css new line after element
  • css calc not working properly
  • link disabled jquery
  • MS Access SQL Issue with OR AND Operators
  • set textarea value using php
  • php merge two arrays into one
  • php form with google captcha
  • update current row in php
  • php date format mysql
  • mysql pdo select query
  • php function return an array
  • PHP mysqldump database to sql file
  • display image query
  • php create array using foreach loop
  • associative array php
  • group_concat in mysql laravel
  • php array_push empty
  • mysql on update cascade not working
  • pdo insert prepared statement
  • mysql insert ignore vs on duplicate key
  • mysql insert into existing row
  • mysql multi insert query
  • mysql group by show all rows
  • php change profile image
  • combine multiple arrays into one array php
  • how to sum values in an array
  • how to get sum of the total time
  • How to Create Facebook live stream without notification using API ?
  • on button click make div visible
  • css affect another element on hover
  • insert line after paragraph word
  • shadow
    Privacy Policy - Terms - Contact Us © bighow.org