Adding a new column to query

[cpampas]

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 :

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

 

[theDBguy]

Hi. Check out the IIf() function.

 

[cpampas]

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]

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]

There is probably a better way to do this, but this seems to work with the data you've provided:

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
;

 

[plog]

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:

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:

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]

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]

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

 

[cheekybuddha]

Hi,

I'm glad you got your solution!

d

 

[theDBguy]

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.