Adding a new column to query (1 Viewer)

cpampas

Registered User.
Local time
Today, 02:46
Joined
Jul 23, 2012
Messages
218
Good morning,
I wonder if there is a way to achieve this :

I have a table with 4 fields ( id, jogoPla,Plaganha, bookie).



id​
jogoPla​
Plaganha​
bookie​
20​
item 1
1,42​
pinnacle​
47​
item 1
1,43​
1xbet​
10​
item 2
2,08​
1xbet​
21​
item 3
2,56​
1xbet​
36​
item 3
2,71​
pinnacle​
56​
item 4
2,7​
pinnacle​
66​
item 5
1,66​
pinnacle​
98​
item 5
1,69​
1xbet​
11​
item 6
2,15​
1xbet​
14​
item 7
2,49​
pinnacle​
15​
item 7
2,5​
1xbet​


Is it possible to query this data and add a new column with the result for each Item of (Plaganha where bookie = pinnacle) / (plaganha where bookie=1xBet)

In this case the desired output would be this :





idjogoPlaPlaganhabookiediff
20item 11,42pinnacle0,993007
47item 11,431xbet
10item 22,081xbet
21item 32,561xbet
36item 32,71pinnacle1,058594
56item 42,7pinnacle
66item 51,66pinnacle0,982249
98item 51,691xbet
11item 62,151xbet
14item 72,49pinnacle0,996
15item 72,51xbet
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:46
Joined
Oct 29, 2018
Messages
21,358
Hi. Check out the IIf() function.
 

cpampas

Registered User.
Local time
Today, 02:46
Joined
Jul 23, 2012
Messages
218
hi,
I would use the iff() function , but (Plaganha where bookie = pinnacle) is on one record and (plaganha where bookie=1xBet) is on another record.
How to make it a calculated field in a query ?
thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:46
Joined
Oct 29, 2018
Messages
21,358
hi,
I would use the iff() function , but (Plaganha where bookie = pinnacle) is on one record and (plaganha where bookie=1xBet) is on another record.
How to make it a calculated field in a query ?
thanks
Hi. I can't tell from just looking at your sample, which records you were using for the calculation, but try using DLookup().
Sent from phone...
 

cheekybuddha

AWF VIP
Local time
Today, 09:46
Joined
Jul 21, 2014
Messages
2,237
There is probably a better way to do this, but this seems to work with the data you've provided:
SQL:
SELECT
  t1.*,
  IIf(t1.bookie = 'pinnacle', t2.diff, NULL) AS diff
FROM YourTable t1         -- <-- *** CHANGE TO YOUR TABLE NAME HERE, AND REMOVE THIS COMMENT ***
LEFT JOIN (
  SELECT
    jogoPla,
    ROUND(MAX(Pinnacle) / MAX([1xbet]), 6) AS diff
  FROM (
    SELECT
      jogoPla,
      bookie,
      SUM(Plaganha) AS PlaganhaTotal,
      IIf(bookie = 'pinnacle', SUM(Plaganha), NULL) AS Pinnacle,
      IIf(bookie = '1xbet', SUM(Plaganha), NULL) AS 1xbet
    FROM (
      SELECT
        *
      FROM YourTable         -- <-- *** CHANGE TO YOUR TABLE NAME HERE, AND REMOVE THIS COMMENT ***
      WHERE bookie IN ('pinnacle', '1xbet')
      ORDER BY
        jogoPla,
        bookie
    )
    GROUP BY
      jogoPla,
      bookie
  )
  GROUP BY
    jogoPla
) t2
       ON t1.jogoPla = t2.jogoPla
;
 
Last edited:

plog

Banishment Pending
Local time
Today, 04:46
Joined
May 11, 2011
Messages
11,613
This will produce the results you expect based on your starting data set. Its going to take a sub query and you need to replace all instance of 'YourTableName' with the name of your table:

Code:
SELECT YourTableName.jogoPla, YourTableName_1.Plaganha AS Denominator, YourTableName.bookie AS pinnacle
FROM YourTableName INNER JOIN YourTableName AS YourTableName_1 ON YourTableName.jogoPla = YourTableName_1.jogoPla
WHERE (((YourTableName.bookie)="pinnacle") AND ((YourTableName_1.bookie)="1xbet"));

Paste that into a query and name it 'sub1'. Then to get the results you want use this query:

Code:
SELECT YourTableName.id, YourTableName.jogoPla, YourTableName.Plaganha, YourTableName.bookie, IIf(IsNull([Denominator])=False,[Plaganha]/[Denominator]) AS diiff
FROM YourTableName LEFT JOIN sub1 ON (YourTableName.bookie = sub1.pinnacle) AND (YourTableName.jogoPla = sub1.jogoPla);
 

cpampas

Registered User.
Local time
Today, 02:46
Joined
Jul 23, 2012
Messages
218
Plog and cheekybuddha,
I have tested your queries with my data and they return the exact same correct result
I might go with cheekybuddha query, but let me ask you, can I adapt this query so that 2 more columns would be added with the same calculation (diff2 and diff) ? the fact is that besides de field"Plaganha" I have : "PlaEmpata" and "PlaPerde",

I ve been trying to modifiy it but it s not that easy to me . By the way, when building these queries wich method do you use ? do you first go to the query wizard ? or you build them from scratch ?
Thanks for the kind help
 

cpampas

Registered User.
Local time
Today, 02:46
Joined
Jul 23, 2012
Messages
218
I built two identical queries to the other fields where I wanted to have same procedure, and it worked just fine
thanks a lot for your help
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:46
Joined
Oct 29, 2018
Messages
21,358
I built two identical queries to the other fields where I wanted to have same procedure, and it worked just fine
thanks a lot for your help
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom