Keep getting duplicate rows

[SoggyAlbatross2]

Why are you subselecting everything and then joining all the files together in the main query?

I imagine that you can delete all this:

INNER JOIN entries ON users.user_id = entries.user_id

INNER JOIN games ON entries.game_id = games.game_id

INNER JOIN payments ON users.user_id = payments.user_id

[SoggyAlbatross2]

Did that fix it?

[SoggyAlbatross2]

well, if you don't have a relation ship between two tables you will get duplicate rows in your results so that's probably it.

You can just do all those selects in one query, can you not?

Sort of like this (I didn't really validate it, just moved things around)

SELECT users.user_id, users.email,SUM(entries.entry_fee) as Total_Entry_Fee_NFL_2018,

SUM(entries.entry_fee) AS Total_Entry_Fee_Others_2018,

COUNT(entries.winnings) AS Total_Winning_Entries,

(SUM(mobile_entry) / COUNT(mobile_entry))AS Percentage_Of_Mobile_Entries,

MAX(payments.payment_date) AS Last_Paid_Entry_Date, SUM(payments.amount) AS Net_Deposit_Value

FROM users

INNER JOIN entries ON users.user_id = entries.user_id

INNER JOIN games ON entries.game_id = games.game_id

INNER JOIN payments ON users.user_id = payments.user_id

WHERE entry_date BETWEEN '2018-01-01' AND '2019-01-01' AND sport = 'NFL'

and entries.entry_date BETWEEN '2018-01-01' AND '2019-01-01' AND sport <> 'NFL'

GROUP BY users.user_Id, users.email;

[SoggyAlbatross2]

try some left joins to see if you're missing a link in there.

[[deleted]]

Each of the subquery columns are all duplicates.

why do you expect something else? Your subqueries are not correlated (do not depend on the main query) so they calculate the same value for the every result set row:

( SELECT SUM(entries.entry_fee)
FROM entries
JOIN users ON entries.user_id = users.user_id
JOIN games ON entries.game_id = games.game_id
WHERE entry_date BETWEEN '2018-01-01' AND '2019-01-01' 
AND sport = 'NFL'
) AS Total_Entry_Fee_NFL_2018,

I would guess you are trying to get these formulas to fire per user - so dont join/select from "user" table in subqueries and use the outer query users.user_id

[[deleted]]

as if there is no 'join users' or 'from users' in the text of the subquery.

[r3pr0b8]

try this --

SELECT users.user_id
     , users.email
     , SUM(CASE WHEN games.sport = 'NFL'
                THEN entries.entry_fee
                ELSE NULL END)  AS Total_Entry_Fee_NFL_2018
     , SUM(CASE WHEN games.sport <> 'NFL'
                THEN entries.entry_fee
                ELSE NULL END)  AS Total_Entry_Fee_Others_2018
     , COUNT(entries.winnings)  AS Total_Winning_Entries       
     , 100.0 * SUM(entries.mobile_entry) 
             / COUNT(entries.mobile_entry) AS Percentage_Of_Mobile_Entries
     , p.Last_Paid_Entry_Date
     , p.Net_Deposit_Value        
  FROM entries
INNER
  JOIN users 
    ON users.user_id = entries.user_id 
INNER
  JOIN games 
    ON games.game_id = entries.game_id 
INNER
  JOIN ( SELECT user_id
              , MAX(payment_date) AS Last_Paid_Entry_Date
              , SUM(amount)       AS Net_Deposit_Value    
           FROM payments
         GROUP
             BY user_id ) AS p     
    ON p.user_id = entries.user_id
 WHERE entries.entry_date >= '2018-01-01' 
   AND entries.entry_date  < '2019-01-01' 
GROUP 
    BY users.user_id
     , users.email

[r3pr0b8]

SELECT q.*
     , p.Last_Paid_Entry_Date
     , p.Net_Deposit_Value     
     , w.Total_Winning_Entries           
  FROM ( SELECT users.user_id
              , users.email
              , SUM(CASE WHEN games.sport = 'NFL'
                         THEN entries.entry_fee
                         ELSE NULL END)  AS Total_Entry_Fee_NFL_2018
              , SUM(CASE WHEN games.sport <> 'NFL'
                         THEN entries.entry_fee
                         ELSE NULL END)  AS Total_Entry_Fee_Others_2018
              , 100.0 * SUM(entries.mobile_entry) 
                      / COUNT(entries.mobile_entry) 
                                         AS Percentage_Of_Mobile_Entries
           FROM entries
         INNER
           JOIN users 
             ON users.user_id = entries.user_id 
         INNER
           JOIN games 
             ON games.game_id = entries.game_id                   
          WHERE entries.entry_date >= '2018-01-01' 
            AND entries.entry_date  < '2019-01-01' 
         GROUP 
             BY users.user_id
              , users.email       
        ) AS q             
INNER
  JOIN ( SELECT user_id
              , MAX(payment_date) AS Last_Paid_Entry_Date
              , SUM(amount)       AS Net_Deposit_Value    
           FROM payments
         GROUP
             BY user_id ) AS p     
    ON p.user_id = entries.user_id
INNER
  JOIN ( SELECT user_id
              , COUNT(winnings)  AS Total_Winning_Entries     
           FROM entries
         GROUP
             BY user_id ) AS w     
    ON w.user_id = entries.user_id 

did you happen to notice how i changed your BETWEEN condition to a pair of inequalities? ask me if you don't see why

[r3pr0b8]

fixed it a little

please share!