Results 1 to 10 of 10

Thread: eliminate multiple zeros

  1. #1

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

    eliminate multiple zeros

    Hi.

    Code:
    select Collected_WorkstationCode,  sum(gross2) as V,
    CASE WHEN  gross < 0 THEN sum(gross) ELSE sum(0) END AS C
    from [dbo].[EAI_SourceData] where cinema = 01
    and COLLECTED_DATE = '20200913'   and Collected_WorkstationName = 'Renbox4'
    group by Collected_WorkstationCode,gross
    Gives:


    Code:
    Collected_WorkstationCode	V	C
    RenBox4	0,00	-7,50
    RenBox4	2823,02	0,00
    RenBox4	0,00	0,00
    RenBox4	0,00	0,00
    RenBox4	0,00	0,00
    RenBox4	0,00	0,00
    RenBox4	0,00	0,00
    How can I eliminate the zero values?
    Or is there a better way to write the query?
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

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

    Re: eliminate multiple zeros

    Your "gross" in your GROUP BY irritates me. I'm surprised this works, since you're grouping on an aggregated field.

    I would have expected: GROUP BY Collected_WorkstationCode
    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

  3. #3

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

    Re: eliminate multiple zeros

    Me too but I get, not contained in either an aggregate function or the GROUP BY

    I have fixed this with A UNION AND CTE but now I have another issue:
    Code:
    With V_CTE(Collected_WorkstationCode,V,C)
    AS
    (select Collected_WorkstationCode,  sum(gross2) as V, 0 as C
    from [dbo].[EAI_SourceData] where cinema = 01
    and COLLECTED_DATE = '20200913'   and Collected_WorkstationName = 'Renbox4'
    group by Collected_WorkstationCode
    UNION ALL
    --Second Refund values insert
    select Collected_WorkstationCode,  0 as V, sum(gross) -- * -1 as C
    from [dbo].[EAI_SourceData] where cinema = 01
    and COLLECTED_DATE = '20200913'  and Collected_WorkstationName = 'Renbox4'
    and IsRefund = 1
    group by Collected_WorkstationCode) 
    select * from V_CTE 
    order by Collected_WorkstationCode
    Collected_WorkstationCode V C
    RenBox4 0,00 -7,50
    RenBox4 2823,02 0,00

    In need another column that will sum this up but I need the V values to sum up with a positive C ( * -1 as C) and the C value to stay negative,

    SO I need something like

    Collected_WorkstationCode V C SUMMED
    RenBox4 0,00 -7,50 -7,50
    RenBox4 2823,02 0,00 2830,52
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  4. #4

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

    Re: eliminate multiple zeros

    This is what I did:
    Collected_WorkstationCode Status Value Total C
    RenBox4 C -7,50 7,50 -7,50
    RenBox4 V 2823,02 0,00 2823,02

    I have a hidden column 'Total'
    What I need to do is when status is 'C' then add the total as leave it as is.
    So something like
    Code:
    select Collected_WorkstationCode,Status,Value,Total,CASE WHEN  Status = 'V' THEN sum(value + total) ELSE Value END AS C from V_CTE 
    group by  Collected_WorkstationCode,Status,Value,Total
    order by Collected_WorkstationCode
    Of course this will not work as the total in status column ='V' is zero. I don't know how I can add the above column to the below.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

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

    Re: eliminate multiple zeros

    Quote Originally Posted by Zvoni View Post
    Your "gross" in your GROUP BY irritates me. I'm surprised this works, since you're grouping on an aggregated field.

    I would have expected: GROUP BY Collected_WorkstationCode
    Quote Originally Posted by sapator View Post
    Me too but I get, not contained in either an aggregate function or the GROUP BY
    You get that because of this:
    Code:
    CASE WHEN  gross < 0 THEN sum(gross) ELSE sum(0) END AS C
    The use of gross in the case statement is outside of an aggregate...

    It probably should have been written like this:
    Code:
    Sum(CASE WHEN  gross < 0 THEN gross ELSE 0 END) AS C
    Now Gross is completely inside of an aggregate, and doesn't need to be in the GROUP BY.

    -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
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: eliminate multiple zeros

    And then to eliminate the zeros ...
    use a HAVING:

    Code:
    HAVING V > 0 and C > 0
    Having works like a WHERE but for aggregate fields.

    -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??? *

  7. #7

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

    Re: eliminate multiple zeros

    Hi.
    Thanks for that but either way I would still have to sum 2 different columns as I state next.
    I have no idea how I can do this
    Thanks.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

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

    Re: eliminate multiple zeros

    Quote Originally Posted by sapator View Post
    This is what I did:
    Collected_WorkstationCode Status Value Total C
    RenBox4 C -7,50 7,50 -7,50
    RenBox4 V 2823,02 0,00 2823,02

    I have a hidden column 'Total'
    What I need to do is when status is 'C' then add the total as leave it as is.
    So something like
    Code:
    select Collected_WorkstationCode,Status,Value,Total,CASE WHEN  Status = 'V' THEN sum(value + total) ELSE Value END AS C from V_CTE 
    group by  Collected_WorkstationCode,Status,Value,Total
    order by Collected_WorkstationCode
    Of course this will not work as the total in status column ='V' is zero. I don't know how I can add the above column to the below.
    Why using a sum at all, if you're grouping by all of the fields?


    I don't know how I can add the above column to the below.
    Wait... are you after a RUNNING TOTAL?

    -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??? *

  9. #9

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

    Re: eliminate multiple zeros

    I need the total of V '2823,02' added to the total of Total of C '7,50'
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  10. #10

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

    Re: eliminate multiple zeros

    This is closer:
    Code:
    With V_CTE(Collected_WorkstationCode,Status,Value,Total)
    AS
    (select Collected_WorkstationCode, 'V' as Status, sum(gross2) as Value, sum(gross2) as Total
    from [dbo].[EAI_SourceData] where cinema = 01
    and COLLECTED_DATE = '20200913'   and Collected_WorkstationName = 'Renbox4'
    group by Collected_WorkstationCode
    UNION ALL
    --Second Refund values insert
    select Collected_WorkstationCode, 'C' as Status, sum(gross)  as Value, sum(gross) * -1 as Total
    from [dbo].[EAI_SourceData] where cinema = 01
    and COLLECTED_DATE = '20200913'  and Collected_WorkstationName = 'Renbox4'
    and IsRefund = 1
    group by Collected_WorkstationCode)  
    select Collected_WorkstationCode,Status,Value, sum(Total) OVER (PARTITION BY  Collected_WorkstationCode) as Total2  from V_CTE
    Collected_WorkstationCode Status Value Total2
    RenBox4 C -7,50 2830,52
    RenBox4 V 2823,02 2830,52

    However now the Total of C column get the complete total 2830,52 but it shoud get -7,5 only.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

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