Thread: Help with query

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2017
    Posts
    2
    Rep Power
    0

    Help with query


    So I want to get all values summed or averaged by date, I am still getting multiple entries per day and not sure why. Please help!

    DECLARE @StartDate DateTime SET @StartDate = '2017-08-01'
    DECLARE @EndDate Datetime SET @EndDate = '2017-08-31'

    SELECT DISTINCT

    pl.PlayerID,
    y.FirstName,
    y.LastName,
    PromotionName,
    DateRedeemed,
    Offer_Amount,
    count(SessionCount) as SessionCount,
    sum(LiveMins) as LiveMins,
    sum(LobbyMins) as LobbyMins,
    sum(CashPlay) as CashPlay,
    sum(ChipPlay) as ChipPlay,
    avg(BetAvg) as [Avg],
    sum(CheckOutAmt) as 'Walk With',
    sum(WinLoss) as WinLoss,
    sum(TheoLoss) as TheoLoss,
    count(NumHands) as NumHands,
    Convert(Datetime, LastPlayDate,120) as 'LastPlayDate',
    CreateDTM as 'Creation Date-Time'
    --CASE WHEN d.PlayerID = 'MISSING' then '0' ELSE 'd.PlayerID' END AS --PlayerID


    FROM HotelOffers.dbo.Players pl
    --bravotglog.dbo.DailyPitData d


    LEFT JOIN bravotglog.dbo.DailyPitData d on d.PlayerID = pl.PlayerID
    LEFT JOIN HotelOffers.dbo.Offers o on o.PlayerID = pl.PlayerID
    LEFT JOIN hoteloffers.dbo.Promotions p on p.PromoID = o.PromoID
    LEFT JOIN HotelOffers.dbo.Players y on pl.PlayerID = y.PlayerID

    WHERE DateRedeemed is not null
    AND DateRedeemed BETWEEN @startdate and @enddate
    AND p.PromoType= '2'






    GROUP BY

    pl.PlayerID,
    y.FirstName,
    y.LastName,
    PromotionName,
    DateRedeemed,
    Offer_Amount,
    LastPlayDate,
    CreateDTM


    ORDER BY PlayerID Asc
  2. #2
  3. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,382
    Rep Power
    9645
    Then that means one or more of the other 7 columns had different values and thus the rows couldn't be combined together.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2017
    Posts
    2
    Rep Power
    0
    How would I get them to sum up the different values and group together by date?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,727
    Rep Power
    4288
    the first thing to do when analyzing any GROUP BY query that seems to be returning duplicates, is to remove the GROUP BY, make sure the PK from every table in the FROM clause is included in the SELECT list, and massage your ORDER BY so that the duplicates are easier to spot

    i'll bet you a beer that your joins include several one-to-many relationships which are compounded against each other in what are called cross join effects (i.e. not a complete cross join, but definitely within the scope of each key)

    for example, i would expect a player to have multiple offers as well as multiple promotions, so these will cross join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,727
    Rep Power
    4288
    also, why are you joining Players pl to Players y?

    also, the IS NOT NULL check here is redundant --
    Code:
    WHERE DateRedeemed is not null
    AND DateRedeemed BETWEEN @startdate and @enddate
    also, p.PromoType= '2' means that the join to Promotions should be INNER, not LEFT

    also, your ORDER BY will create an error, since PlayerID is ambiguous

    also, this is clearly Microsoft SQL Server, so i'm moving it to that forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo