SUM with value of COUNT

[micky007]

Hi again,

Thanks again for the help you guys have all provided me so far and helping me learn and understand more from both PHP and MySQL. Based off previous help from MySQL I've decided to reduce the amount of PHP coding in my script by using JOIN queries. I'm a little stuck on the below query:

SELECT players.player_id, players.deal_id, affiliate_deals.cpa,
COUNT(players.deal_id) AS TotalPlayersByDeal,
SUM(affiliate_deals.cpa * TotalPlayersByDeal) affiliate_cpa_earnings
FROM players
INNER JOIN affiliate_deals ON affiliate_deals.affiliate_deal_id=players.deal_id
WHERE players.affiliate_id=1 AND affiliate_deals.type = 'CPA' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1 OR players.affiliate_id=1 AND affiliate_deals.type = 'Hybrid' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1
GROUP BY players.deal_id

The issue is the SUM part, how do i go about multiplying the affiliate_deals.cpa by the number of players with the same players.deal_id value?

I didn't think my example above would work and it sure didn't providing me with the error:

Quote

#1054 - Unknown column 'TotalPlayersByDeal' in 'field list'

My guess is i cant use the TotalPlayersByDeal part in the SUM query. As for a work-around I'm struggling to think of a way and this is where i need your help if possible.

Thank you!

[Barand]

Aliases are applied after the query runs but prior to output so they are not available within the query. You need to use

...
COUNT(players.deal_id) AS TotalPlayersByDeal,
SUM(affiliate_deals.cpa * COUNT(players.deal_id)) as affiliate_cpa_earnings
...

 

[micky007]

8 minutes ago, Barand said:

Aliases are applied after the query runs but prior to output so they are not available within the query. You need to use

...
COUNT(players.deal_id) AS TotalPlayersByDeal,
SUM(affiliate_deals.cpa * COUNT(players.deal_id)) as affiliate_cpa_earnings
...

 

I've already tried what you suggested but i get this error:

Quote

#1111 - Invalid use of group function

SELECT players.player_id, players.deal_id, affiliate_deals.cpa,
COUNT(players.deal_id) AS TotalPlayersByDeal,
SUM(affiliate_deals.cpa * COUNT(players.deal_id)) as affiliate_cpa_earnings
FROM players
INNER JOIN affiliate_deals ON affiliate_deals.affiliate_deal_id=players.deal_id
WHERE players.affiliate_id=1 AND affiliate_deals.type = 'CPA' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1 OR players.affiliate_id=1 AND affiliate_deals.type = 'Hybrid' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1
GROUP BY players.deal_id

 

Edited February 26, 2019 by micky007

[requinix]

It would be equivalent to

SUM(affiliate_deals.cpa) * COUNT(players.deal_id)

 

[micky007]

8 hours ago, requinix said:

It would be equivalent to

SUM(affiliate_deals.cpa) * COUNT(players.deal_id)

 

Thanks for thats, how ever I'm not getting the right result and I'm trying to find out whats actually happening.

SELECT players.player_id, players.deal_id, affiliate_deals.cpa,
COUNT(players.deal_id) AS TotalPlayersByDeal,
SUM(affiliate_deals.cpa) * COUNT(players.deal_id) AS affiliate_cpa_earnings
FROM players
INNER JOIN affiliate_deals ON affiliate_deals.affiliate_deal_id=players.deal_id
WHERE players.affiliate_id=1 AND affiliate_deals.type = 'CPA' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1 OR players.affiliate_id=1 AND affiliate_deals.type = 'Hybrid' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1
GROUP BY players.deal_id

RESULT:

player_id		deal_id		cpa	TotalPlayersByDeal			affiliate_cpa_earnings 	
1 			1 		15 	8 					960
14		 	2 		50 	1 					50

Not sure how 15 * 8 = 960?

[micky007]

Aaaaaaahhhh i see whats going on, I've done it so its calculating the total of the CPA that have the same deal_id value and then multiplying it by the total number of players. So its doing 15 x 8 x 8 which is 960.

So all i have to do is remove the

* COUNT(players.deal_id)

  part from the query and it works fine.

Well that was simple enough.....

[requinix]

Well no, there's still more to this that should be addressed.

SELECT players.player_id, players.deal_id, affiliate_deals.cpa,
COUNT(players.deal_id) AS TotalPlayersByDeal,
SUM(affiliate_deals.cpa) * COUNT(players.deal_id) AS affiliate_cpa_earnings
FROM players
INNER JOIN affiliate_deals ON affiliate_deals.affiliate_deal_id=players.deal_id
WHERE players.affiliate_id=1 AND affiliate_deals.type = 'CPA' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1 OR players.affiliate_id=1 AND affiliate_deals.type = 'Hybrid' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1
GROUP BY players.deal_id

When GROUPing BY something, you should only be SELECTing that particular something. Or if it's unique to a table, you could get it and other columns from that table.

You are grouping on the players.deal_id, which is not a unique column in the players table. AFAIK. That means it only makes sense to SELECT players.deal_id. In MySQL, if you try other columns then you'll get some almost random value from one of the rows that got grouped together; some other databases like PostgreSQL are strict about not letting you do this.

You want the player_id, deal_id, and cpa. The deal_id is unique to affiliate_deals, I think, so if you GROUP BY deal_id (either in the players or affiliate_deals table, it's the same value) then you can also get the cpa, but you can't safely get the player_id. You need to

GROUP BY players.deal_id /* aka affiliate_deals.affiliate_deal_id */, players.player_id

That will very likely change some of your numbers, but even if you don't recognize that now it is changing for the better. Or if it isn't then it's pointing out a problem with this query you didn't know you had.

[micky007]

17 hours ago, requinix said:

Well no, there's still more to this that should be addressed.

SELECT players.player_id, players.deal_id, affiliate_deals.cpa,
COUNT(players.deal_id) AS TotalPlayersByDeal,
SUM(affiliate_deals.cpa) * COUNT(players.deal_id) AS affiliate_cpa_earnings
FROM players
INNER JOIN affiliate_deals ON affiliate_deals.affiliate_deal_id=players.deal_id
WHERE players.affiliate_id=1 AND affiliate_deals.type = 'CPA' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1 OR players.affiliate_id=1 AND affiliate_deals.type = 'Hybrid' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1
GROUP BY players.deal_id

When GROUPing BY something, you should only be SELECTing that particular something. Or if it's unique to a table, you could get it and other columns from that table.

You are grouping on the players.deal_id, which is not a unique column in the players table. AFAIK. That means it only makes sense to SELECT players.deal_id. In MySQL, if you try other columns then you'll get some almost random value from one of the rows that got grouped together; some other databases like PostgreSQL are strict about not letting you do this.

You want the player_id, deal_id, and cpa. The deal_id is unique to affiliate_deals, I think, so if you GROUP BY deal_id (either in the players or affiliate_deals table, it's the same value) then you can also get the cpa, but you can't safely get the player_id. You need to

GROUP BY players.deal_id /* aka affiliate_deals.affiliate_deal_id */, players.player_id

That will very likely change some of your numbers, but even if you don't recognize that now it is changing for the better. Or if it isn't then it's pointing out a problem with this query you didn't know you had.

Your kind of right, how ever all i really need is for the Query to work out how much the player_id has earned based off the affiliate_deal  for program_id.

So if i run this query:

SELECT players.deal_id,
COUNT(players.deal_id) AS TotalPlayersByDeal,
SUM(affiliate_deals.cpa) * COUNT(players.deal_id) AS affiliate_cpa_earnings
FROM players
INNER JOIN affiliate_deals ON affiliate_deals.affiliate_deal_id=players.deal_id
WHERE players.affiliate_id=1 AND affiliate_deals.type = 'CPA' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1 OR players.affiliate_id=1 AND affiliate_deals.type = 'Hybrid' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1
GROUP BY players.deal_id, players.player_id

Then that will display

Quote

deal_id    TotalPlayersByDeal    affiliate_cpa_earnings    

1    1    15    
1    1    15    
1    1    15    
1    1    15    
1    1    15    
1    1    15    
1    1    15    
1    1    15    
2    1    50    

 

So as the result shows the records of each player that's earned the affiliate some money all i want is for the affiliate_cpa_earnings to be grouped together so my aim is to just have the 1 result showing something like this

Quote

TotalRevenue

170

 

[requinix]

Yeah, except while you added the player_id grouping you removed it from the columns to return. So there's no point.

Take the query you just demonstrated and remove the GROUP BY on player_id.

[micky007]

17 hours ago, requinix said:

Yeah, except while you added the player_id grouping you removed it from the columns to return. So there's no point.

Take the query you just demonstrated and remove the GROUP BY on player_id.

I'm not sure which Query your wanting me to use.

Did you mean this one?

Quote

SELECT players.deal_id,
COUNT(players.deal_id) AS TotalPlayersByDeal,
SUM(affiliate_deals.cpa) AS affiliate_cpa_earnings
FROM players
INNER JOIN affiliate_deals ON affiliate_deals.affiliate_deal_id=players.deal_id
WHERE players.affiliate_id=1 AND affiliate_deals.type = 'CPA' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1 OR players.affiliate_id=1 AND affiliate_deals.type = 'Hybrid' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1
GROUP BY players.deal_id

deal_id    TotalPlayersByDeal    affiliate_cpa_earnings    
1    8    120    
2    1    50    

 

OR

Quote

SELECT players.deal_id,
COUNT(players.deal_id) AS TotalPlayersByDeal,
SUM(affiliate_deals.cpa) * COUNT(players.deal_id) AS affiliate_cpa_earnings
FROM players
INNER JOIN affiliate_deals ON affiliate_deals.affiliate_deal_id=players.deal_id
WHERE players.affiliate_id=1 AND affiliate_deals.type = 'CPA' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1 OR players.affiliate_id=1 AND affiliate_deals.type = 'Hybrid' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1
GROUP BY players.deal_id

deal_id    TotalPlayersByDeal    affiliate_cpa_earnings    
1    8    960    
2    1    50    

 

@Barand

[micky007]

For some reason its not letting me add any text after mentioning a user so I'm having to finish my reply in a new one. What i was saying was barand has informed me to look into Data Normalisation so I'm looking into that too now.

[requinix]

4 hours ago, micky007 said:

I'm not sure which Query your wanting me to use.

Presumably "the query you just demonstrated" would be the query you had included in the post immediately before mine:

SELECT players.deal_id,
COUNT(players.deal_id) AS TotalPlayersByDeal,
SUM(affiliate_deals.cpa) * COUNT(players.deal_id) AS affiliate_cpa_earnings
FROM players
INNER JOIN affiliate_deals ON affiliate_deals.affiliate_deal_id=players.deal_id
WHERE players.affiliate_id=1 AND affiliate_deals.type = 'CPA' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1 OR players.affiliate_id=1 AND affiliate_deals.type = 'Hybrid' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1
GROUP BY players.deal_id, players.player_id