Results 1 to 13 of 13

Thread: trouble summing

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    trouble summing

    Code:
      select T.GrossValue,T.GrossValue + IsNull(sum(F.GrossValue), 0) as value from tblDWTicket T
       left join tblDWBookingFee F on T.Transnumber = F.Transnumber and T.CinemaCode= F.CinemaCode
      where t.CinemaCode=21 and T.transnumber = 10551105
      group by T.GrossValue,F.GrossValue,T.CinemaCode,T.TransNumber
    I have an issue here.
    select T.GrossValue from tblDWTicket T where t.CinemaCode=21 and T.transnumber = 10551105
    this will give me:

    GrossValue
    3,75
    6,50

    select F.Grossvalue from tblDWBookingFee F where F.CinemaCode=21 and F.TransNumber = 10551105
    This gives:
    Grossvalue
    1,00

    Now the problem is that when I join in the first query, apparently the F.Grossvalue adds to both rows of the tblDWTicket so instead of getting
    4,75
    6,5
    or
    3,75
    7,5
    I get
    4,75
    7,5

    I need to add this just one time and not two.
    How can I do that?
    Thanks
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: trouble summing

    Ok.
    The best I could do so far is this (yes for codetags,yes for codetags...gogogogo! ) :
    Code:
       with tickets
    (transnumber,cinema,GrossValue,fullprice)
    as(select T.TransNumber,T.CinemaCode,T.GrossValue,sum(TMP.PPack_curPriceEach) from tblDWTicket T 
      inner join #tempTick TMT on T.TicketTypeHOCode =TMT.TType_strHOCode 
      left join #tempPack TMP on TMP.PGroup_strCode = T.Pricegroup and TMP.Price_strCode = TMT.TType_strCode
        where T.SalesTransDate > '20180516' and T.TransStatus not in ('C','R') and T.TransType = 'TKT'
    --	and TMP.PPack_strCode is not null
     and T.CinemaCode = 21 and T.TransNumber = 10609062
    	and T.SalesTransWorkstationCode like 'K%'
    	group by T.TransNumber,T.CinemaCode,T.GrossValue,T.TransSequence
      )
     ,
     tickets2 (cinema,transnumber,GrossValue,fullprice) as
      (select tickets.cinema,tickets.transnumber,sum(tickets.GrossValue),sum(tickets.fullprice) from tickets 
     group by tickets.cinema,tickets.transnumber
    )
    
    --select * from tickets2
    
    ,
    
    
      final (cinema,transnumber,GrossValue,fullprice) as
      (select tickets2.cinema,tickets2.transnumber,tickets2.GrossValue + IsNull(sum(F.GrossValue), 0),tickets2.fullprice from tickets2 
       left join tblDWBookingFee F on tickets2.Transnumber = F.Transnumber and tickets2.cinema= F.CinemaCode  
       where F.TransStatus not in('R','C')
       group by tickets2.cinema,tickets2.transnumber,tickets2.fullprice,tickets2.GrossValue
       )
    
    select * from final
    where final.GrossValue <> final.fullprice
    
    
      drop table  #tempPack
      drop table #tempTick
    I triple group it, now the problem I have is that sometimes #tempPack is null because the values are on #tempTick , so I left joined it and I am thinking of something like Case , so if the
    TMP.PPack_curPriceEach is null ,sum it by a tempTick value, else sum it by a #tempPack .
    I'm struggling I will get back if I have issues.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: trouble summing

    Got it.
    But I would still like to know how I can fix my original code as I went through China for this one

    Code:
      with tickets
    (transnumber,cinema,GrossValue,fullprice)
    as(select T.TransNumber,T.CinemaCode,T.GrossValue--,sum(TMP.PPack_curPriceEach)
    ,case when TMP.PGroup_strCode is null then sum(TMPP.Price_curPrice) else sum(TMP.PPack_curPriceEach) end as fullprice
    from tblDWTicket T 
      inner join #tempTick TMT on T.TicketTypeHOCode =TMT.TType_strHOCode 
      inner join #tempPrice TMPP on TMPP.PGroup_strCode = T.PriceGroup and TMPP.Price_strCode = T.PriceCode
      left join #tempPack TMP on TMP.PGroup_strCode = T.Pricegroup and TMP.Price_strCode = TMT.TType_strCode
        where T.SalesTransDate > '20180516' and T.TransStatus not in ('C','R') and T.TransType = 'TKT'
    --	and TMP.PPack_strCode is not null
    -- and T.CinemaCode = 21 and T.TransNumber = 10609062
    	and T.SalesTransWorkstationCode like 'K%'
    	group by T.TransNumber,T.CinemaCode,T.GrossValue,t.TransSequence,TMP.PGroup_strCode
      )
     ,
     tickets2 (cinema,transnumber,GrossValue,fullprice) as
      (select tickets.cinema,tickets.transnumber,sum(tickets.GrossValue),sum(tickets.fullprice) from tickets 
     group by tickets.cinema,tickets.transnumber
    ),
    
    --select * from tickets2
    
    
      final (cinema,transnumber,GrossValue,fullprice) as
      (select tickets2.cinema,tickets2.transnumber,tickets2.GrossValue + IsNull(sum(F.GrossValue), 0),tickets2.fullprice from tickets2 
       left join tblDWBookingFee F on tickets2.Transnumber = F.Transnumber and tickets2.cinema= F.CinemaCode  
       where (F.TransStatus not in('R','C')) or (F.TransStatus is null)
       group by tickets2.cinema,tickets2.transnumber,tickets2.fullprice,tickets2.GrossValue
       )
    
    select * from final
    where final.GrossValue <> final.fullprice
    
    
      drop table  #tempPack
      drop table #tempTick
      drop table #tempPrice
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: trouble summing

    Question: What's the purpose of this?
    As far as i could understand you, you get 2 T.GrossValue from your Query, but you need the F.GrossValue only once.

    The F-Query: Does it always return just one row for CinemaCode and TransID?
    I'm asking, because in your original code you use sum(F.GrossValue) implying that there could be more than one row from the F-Query.

    If you only need a total sum in the end, why not use a UNION?
    The first part being your T-Query UNION'ed with your F-Query

    Then it's just a SELECT Sum(U.GrossValue) FROM (SELECT T-Query UNION F-Query) AS U
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: trouble summing

    I thought the same thing too... but I'm not sure if he wants two rows, where the extra row is added to either (but not both), or just one row with a grand total. If it's the latter then yeah, a union is the way to go. If the former... it's trickier. And I also suspect, based on his solutions, that the quieries aren't just that simple, but are a bit more complex, which means the solution is going to be equally complex.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: trouble summing

    TG, as i read it: He's writing something like a CinemaTicket Sales-Thingy.

    Something along the lines (my Shopping Basket):
    2 Tickets for Movie 1 = 2 x 5 USD = 10 USD
    1 Ticket for Movie 2 = 1 x 6,50 USD = 6,50 USD
    1 Transaction-Fee = 1 USD
    --------------------------------------------
    Total 17,50 USD
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: trouble summing

    It's trickier because I'm joining 3 temp tables that has the price cards but does not always match.
    Can you give me an example on how the below would be unioned?
    Code:
      final (cinema,transnumber,fullprice,value) as
      (select tickets.cinema,tickets.transnumber,sum(tickets.fullprice),sum(tickets.GrossValue) + IsNull(sum(F.GrossValue), 0) as value from tickets 
       left join tblDWBookingFee F on tickets.Transnumber = F.Transnumber and tickets.cinema= F.CinemaCode
       where F.TransStatus not in('R','C')
      and tickets.cinema = 21 and tickets.transnumber = 10551105
      group by tickets.cinema,tickets.fullprice,tickets.transnumber,tickets.GrossValue,f.GrossValue)
    At this stage I have grouped the transactions and i need to join the fee (that might be empty,one row, more than one row)
    So tickets groupedvalue + (sum of fees)
    Thanks.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: trouble summing

    Put a subquery in the select list to SUM() the fee. JOIN'ing is not always the answer - the JOIN in this case is inflating the row count in one of the tables, which means the DOMAIN isn't the same - so JOIN won't do it.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: trouble summing

    You mean like.
    Select x,f, (select sum(x from table) ),g ... ?
    I found this very difficult t grasp(not an sql expert of course) so I just CTEed, bringing counting all the amount summed into on row and the i joined the fee. That way I am just joining a summed amount + any fees so I don't have double rows.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: trouble summing

    But why? It's just like a sub-routine. It runs the little sub-query for EACH row - so you just need to WHERE clause it appropriately.

    In reality it most likely prepares a result set that is internally used - in a way a join.

    If I was doing this I would actually create a VIEW that collapses the table with a GROUP BY getting down to ONE ROW for this entity. And another VIEW for the other table. Each one down to the same DOMAIN and the JOIN is effortless.

    Having these VIEW's in place allows you to research data issues because you can actually "see" the collapsed view.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: trouble summing

    Quote Originally Posted by szlamany View Post
    But why?
    Because i'm not sure how to do it. An example would be appreciated.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  12. #12

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: trouble summing

    So. anyhow I tried to do a subquery.
    This is what I wrote:
    Code:
      final (cinema,transnumber,GrossValue,summedfee,fullprice) as
      (select tickets.cinema,tickets.transnumber,sum(tickets.GrossValue), 
      (select IsNull(sum(F.GrossValue), 0) from tblDWBookingFee F where tickets.cinema = F.CinemaCode and tickets.transnumber = f.TransNumber and F.TransStatus not in('R','C') )
     as value,sum(tickets.fullprice)
      from tickets 
      where tickets.cinema = 21 and tickets.transnumber = 10551105
      group by tickets.cinema,tickets.transnumber)
      
      select final.cinema,final.transnumber,SUM(final.Grossvalue + final.summedfee),final.fullprice from final -- where final.fullprice <> final.fullprice
      group by final.cinema,final.transnumber,final.fullprice
    szlamany ,is that what you had in mind?
    Is seems to work, although I am using the solution posted earlier. I don't know which is best, I will try to incorporate the last code sample to the working solution to see if I can get rid of one cycle of grouping on tickets2...
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  13. #13

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: trouble summing

    OK.
    The values are correct but I have the issue of <> .
    So If i try to find the difference
    Code:
      select final.cinema,final.transnumber,SUM(final.Grossvalue + final.summedfee) as fee,final.fullprice from final  where final.fullprice <> SUM(final.Grossvalue + final.summedfee)
      group by final.cinema,final.transnumber,final.fullprice,final.cinemadate
      order by final.cinema,final.cinemadate
    I get:
    An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

    So I would either use another CTE to have the summed value or something else. If I use another CTE, I pretty much do not avoid anything I did in my first solution post...

    Edit:
    Did what suggested, I used a having clause and it work. I'm searching for the select list it specifies as using a having like this just does not seem too right...Or is it?
    Code:
      select final.cinema,final.transnumber,SUM(final.Grossvalue + final.summedfee) as fee,final.fullprice from final 
     --  where final.fullprice <> SUM(final.Grossvalue + final.summedfee)
      group by final.cinema,final.transnumber,final.fullprice,final.cinemadate
      having  SUM(final.Grossvalue + final.summedfee) <> final.fullprice 
      order by final.cinema,final.cinemadate
    Edit2. Can't do it with an subquery as I saw some solutions suggests.
    Any help?
    Code:
     select final.cinema,final.transnumber,SUM(final.Grossvalue + final.summedfee) as fee,final.fullprice from final 
     --  where final.fullprice <> SUM(final.Grossvalue + final.summedfee)
     where final.fullprice <> (select SUM(final.Grossvalue + final.summedfee) from final )
      group by final.cinema,final.transnumber,final.fullprice,final.cinemadate
      --having  SUM(final.Grossvalue + final.summedfee) <> final.fullprice 
      order by final.cinema,final.cinemadate
    Edit3. I un summed and it works, but I would still like to know how would I pass the error is i used sum. From my understanding it has to do with the where and group precedence but I can't figure out a solution
    Code:
      select final.cinema,final.transnumber,(final.Grossvalue + final.summedfee) as fee,final.fullprice from final 
     where final.fullprice <> (final.Grossvalue + final.summedfee )
      group by final.cinema,final.transnumber,final.fullprice,final.cinemadate,final.GrossValue,final.summedfee
      order by final.cinema,final.cinemadate
    Last edited by sapator; Jun 22nd, 2018 at 03:22 AM.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width