micky007 Posted February 25, 2019 Share Posted February 25, 2019 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! Quote Link to comment https://forums.phpfreaks.com/topic/308395-sum-with-value-of-count/ Share on other sites More sharing options...
Barand Posted February 26, 2019 Share Posted February 26, 2019 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 ... Quote Link to comment https://forums.phpfreaks.com/topic/308395-sum-with-value-of-count/#findComment-1564830 Share on other sites More sharing options...
micky007 Posted February 26, 2019 Author Share Posted February 26, 2019 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/308395-sum-with-value-of-count/#findComment-1564831 Share on other sites More sharing options...
requinix Posted February 26, 2019 Share Posted February 26, 2019 It would be equivalent to SUM(affiliate_deals.cpa) * COUNT(players.deal_id) Quote Link to comment https://forums.phpfreaks.com/topic/308395-sum-with-value-of-count/#findComment-1564838 Share on other sites More sharing options...
micky007 Posted February 26, 2019 Author Share Posted February 26, 2019 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? Quote Link to comment https://forums.phpfreaks.com/topic/308395-sum-with-value-of-count/#findComment-1564852 Share on other sites More sharing options...
micky007 Posted February 26, 2019 Author Share Posted February 26, 2019 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..... Quote Link to comment https://forums.phpfreaks.com/topic/308395-sum-with-value-of-count/#findComment-1564858 Share on other sites More sharing options...
requinix Posted February 26, 2019 Share Posted February 26, 2019 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. Quote Link to comment https://forums.phpfreaks.com/topic/308395-sum-with-value-of-count/#findComment-1564864 Share on other sites More sharing options...
micky007 Posted February 27, 2019 Author Share Posted February 27, 2019 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 Quote Link to comment https://forums.phpfreaks.com/topic/308395-sum-with-value-of-count/#findComment-1564882 Share on other sites More sharing options...
requinix Posted February 27, 2019 Share Posted February 27, 2019 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. Quote Link to comment https://forums.phpfreaks.com/topic/308395-sum-with-value-of-count/#findComment-1564899 Share on other sites More sharing options...
micky007 Posted February 28, 2019 Author Share Posted February 28, 2019 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 Quote Link to comment https://forums.phpfreaks.com/topic/308395-sum-with-value-of-count/#findComment-1564913 Share on other sites More sharing options...
micky007 Posted February 28, 2019 Author Share Posted February 28, 2019 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. Quote Link to comment https://forums.phpfreaks.com/topic/308395-sum-with-value-of-count/#findComment-1564915 Share on other sites More sharing options...
requinix Posted February 28, 2019 Share Posted February 28, 2019 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 Quote Link to comment https://forums.phpfreaks.com/topic/308395-sum-with-value-of-count/#findComment-1564933 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.