-
Sep 24th, 2020, 07:27 AM
#1
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?
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Sep 24th, 2020, 08:20 AM
#2
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
-
Sep 24th, 2020, 08:44 AM
#3
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
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Sep 24th, 2020, 09:19 AM
#4
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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Sep 24th, 2020, 09:28 AM
#5
Re: eliminate multiple zeros
Originally Posted by Zvoni
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
Originally Posted by sapator
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
-
Sep 24th, 2020, 09:29 AM
#6
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
-
Sep 24th, 2020, 09:35 AM
#7
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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Sep 24th, 2020, 09:48 AM
#8
Re: eliminate multiple zeros
Originally Posted by sapator
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
-
Sep 24th, 2020, 10:39 AM
#9
Re: eliminate multiple zeros
I need the total of V '2823,02' added to the total of Total of C '7,50'
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Sep 24th, 2020, 11:12 AM
#10
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|