November 10, 2011 at 4:54 am
Hi,
I have a requirement to do a SUM of the COUNT within a CASE statement:
SUM(CASE WHEN e.EQUIPMENT_TYPE_CD like '20%' THEN COUNT(e.BOOKING_EQ_REQ_DETAIL_ID) ELSE COUNT(e.BOOKING_EQ_REQ_DETAIL_ID) * 2 END) AS BookedTEU
I was wondering if this would be possilble to achieve as currently when I run the above statement, I get an error.
Any help would be appreciated.
Thanks,
Paul
November 10, 2011 at 5:28 am
I'm guessing the error you're getting is: "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."
In order to do something like that, you'll probably need to use a derived table to obtain your first level of aggregation (the COUNT) and then obtain your SUM from a query against the derived table. If you would like to post the whole query and the table schema along with some sample data, I can give you more details for your particular situation.
November 10, 2011 at 5:41 am
JonFox (11/10/2011)
I'm guessing the error you're getting is: "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."In order to do something like that, you'll probably need to use a derived table to obtain your first level of aggregation (the COUNT) and then obtain your SUM from a query against the derived table. If you would like to post the whole query and the table schema along with some sample data, I can give you more details for your particular situation.
Thanks very much for your reply. Please find the query below:
------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[prBookAllocASAEUR_SUM]
@vessels [nvarchar](4000),
@voyages [nvarchar](4000),
@weeks [nvarchar](4000),
@trades [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
SELECT
t.Week,
t.SERVICE_CD,
t.VESSEL_CD,
t.VOYAGE_CD,
t.BookingGroup,
t.BookedTEU,
t.F_20,
t.F_40,
t.F_45,
t.F_HC,
cast(t.BookedWGT/1000 as decimal(15,2)) as BookedWGT
from
(selectcast(crv.Week as int) as [Week],
i.SERVICE_CD,
i.VESSEL_CD,
i.VOYAGE_CD,
--BookingGroup=
CASE NOMINATION_CLASS
WHEN 'J' THEN '1 - JAPAN'
WHEN 'A' THEN '2 - AOTJ'
WHEN 'M' THEN '3 - MALT'
ELSE (case when b.booking_type_cd='EMP' then '4 - EMPTY'else ba.BookingGroup end)
END,
SUM(CASE WHEN e.EQUIPMENT_TYPE_CD like '20%' THEN e.BOOKING_EQ_REQ_DETAIL_ID ELSE e.BOOKING_EQ_REQ_DETAIL_ID * 2 END) AS BookedTEU,
SUM(CASE WHEN e.EQUIPMENT_TYPE_CD like '20%' THEN e.BOOKING_EQ_REQ_DETAIL_ID ELSE 0 END) AS F_20,
SUM(CASE WHEN e.EQUIPMENT_TYPE_CD IN ('40D86', '40F86', '40O86', '40PPU', '40R86', '40STR', '40T96', '40B86') THEN e.BOOKING_EQ_REQ_DETAIL_ID ELSE 0 END) AS F_40,
SUM(CASE WHEN e.EQUIPMENT_TYPE_CD like '45%' THEN e.BOOKING_EQ_REQ_DETAIL_ID ELSE 0 END) AS F_45,
SUM(CASE WHEN e.EQUIPMENT_TYPE_CD IN ('40D96', '40R96', '40S96', '40F96', '40Q96', '40B96', '40P96', '40T96') THEN e.BOOKING_EQ_REQ_DETAIL_ID ELSE 0 END) AS F_HC,
SUM(isnull(e.GROSS_WGT,0)) as BookedWGT
FROM
MG_BOOKING b
JOINMG_BOOKING_EQUIPMENT_REQ_DETAIL e ON b.BOOKING_ID = e.BOOKING_ID
JOINMG_BOOKING_ITINERARY i ON b.BOOKING_ID = i.BOOKING_ID
JOINMG_LOCATION l on l.location_cd=i.from_location_cd
JOINMGS_BookingAllocation_Groups ba on b.BOOKING_OFFICE_CD=ba.Booking_office_cd
JOINMGS_CurrentReportingVessels AS crv ON i.VESSEL_CD = crv.VESSEL_CD AND i.VOYAGE_CD = crv.VOYAGE_CD
WHERE
ba.TRADE in (select distinct Item From dbo.Split(@trades,','))
ANDb.DELETED_FLG = 'N'
ANDb.BOOKING_STATUS_CD IN ('F', 'P','H', 'I')
ANDb.BOOKING_TYPE_CD IN ('FCL')
ANDb.LAST_UPDATE_DT >= GETDATE() - 90
ANDi.VESSEL_CD in (select distinct Item From dbo.Split(@vessels,','))
ANDi.VOYAGE_CD in (select distinct Item From dbo.Split(@voyages,','))
ANDba.continent_cd in ('eur', 'med')
ANDCRV.week in (select distinct Item From dbo.Split(@weeks,','))
andi.TO_LOCATION_CD not in ('EGPSD','SAJED')
andl.continent_cd in ('eur', 'med')
group by crv.Week,i.SERVICE_CD, i.VESSEL_CD, i.VOYAGE_CD, CASE NOMINATION_CLASS
WHEN 'J' THEN '1 - JAPAN'
WHEN 'A' THEN '2 - AOTJ'
WHEN 'M' THEN '3 - MALT'
ELSE (case when b.booking_type_cd='EMP' then '4 - EMPTY' else ba.BookingGroup end)
END) t
order by 1,2,3,4,5
------------------------------------------------------------------
Please note the changes have to be made in the above query for the part mentioned below:
-----------------------------------------------------------------
SUM(CASE WHEN e.EQUIPMENT_TYPE_CD like '20%' THEN e.BOOKING_EQ_REQ_DETAIL_ID ELSE e.BOOKING_EQ_REQ_DETAIL_ID * 2 END) AS BookedTEU,
SUM(CASE WHEN e.EQUIPMENT_TYPE_CD like '20%' THEN e.BOOKING_EQ_REQ_DETAIL_ID ELSE 0 END) AS F_20,
SUM(CASE WHEN e.EQUIPMENT_TYPE_CD IN ('40D86', '40F86', '40O86', '40PPU', '40R86', '40STR', '40T96', '40B86') THEN e.BOOKING_EQ_REQ_DETAIL_ID ELSE 0 END) AS F_40,
SUM(CASE WHEN e.EQUIPMENT_TYPE_CD like '45%' THEN e.BOOKING_EQ_REQ_DETAIL_ID ELSE 0 END) AS F_45,
SUM(CASE WHEN e.EQUIPMENT_TYPE_CD IN ('40D96', '40R96', '40S96', '40F96', '40Q96', '40B96', '40P96', '40T96') THEN e.BOOKING_EQ_REQ_DETAIL_ID ELSE 0 END) AS F_HC,
-------------------------------------------------------------------
What I want to achieve is:
SUM(CASE WHEN e.EQUIPMENT_TYPE_CD like '20%' THEN COUNT(e.BOOKING_EQ_REQ_DETAIL_ID) ELSE COUNT(e.BOOKING_EQ_REQ_DETAIL_ID) * 2 END) AS BookedTEU
November 10, 2011 at 6:52 am
Thanks! It would be best if you could post table code and sample data; here's an article that explains how to do that: http://www.sqlservercentral.com/articles/Best+Practices/61537/ However, here's a quick stab at an example of how you could go about solving this (greatly simplified, using only one table and some fake data):
CREATE TABLE #MG_BOOKING_EQUIPMENT_REQ_DETAIL
(
BOOKING_EQ_REQ_DETAIL_ID INT,
EQUIPMENT_TYPE_CD VARCHAR(30)
)
INSERT INTO #MG_BOOKING_EQUIPMENT_REQ_DETAIL
SELECT 1, '2001' UNION
SELECT 2, '300x' UNION
SELECT 3, '2020' UNION
SELECT 4, 'XYZ100'
SELECT SUM(BookedTEU) AS BookedTEU
FROM
(
SELECT e.EQUIPMENT_TYPE_CD,
CASE WHEN e.EQUIPMENT_TYPE_CD like '20%' THEN COUNT(e.BOOKING_EQ_REQ_DETAIL_ID) ELSE COUNT(e.BOOKING_EQ_REQ_DETAIL_ID) * 2 END
FROM #MG_BOOKING_EQUIPMENT_REQ_DETAIL AS e
GROUP BY e.EQUIPMENT_TYPE_CD
) AS dt (EQUIPMENT_TYPE_CD, BookedTEU);
DROP TABLE #MG_BOOKING_EQUIPMENT_REQ_DETAIL
Note how the derived is performing the first level of aggregation (the COUNT) and then the results are used to perform the SUM. Obviously this is much simpler than your situation, but hopefully it will get you started. If you need further help, please post the table definitions and some sample data, and I'll try to provide a more detailed example for you. Hope this helps!
November 10, 2011 at 6:57 am
This should do it:
SUM(CASE WHEN e.EQUIPMENT_TYPE_CD like '20%' THEN 1 ELSE 2 END
* CASE WHEN e.BOOKING_EQ_REQ_DETAIL_ID IS NULL THEN 0 ELSE 1 END) AS BookedTEU
This gives you a 1 for each row where e.BOOKING_EQ_REQ_DETAIL_ID is not null.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 10, 2011 at 7:02 am
ChrisM@Work (11/10/2011)
This should do it:
SUM(CASE WHEN e.EQUIPMENT_TYPE_CD like '20%' THEN 1 ELSE 2 END* CASE WHEN e.BOOKING_EQ_REQ_DETAIL_ID IS NULL THEN 0 ELSE 1 END) AS BookedTEU
This gives you a 1 for each row where e.BOOKING_EQ_REQ_DETAIL_ID is not null.
Well, yeah, you COULD do it that way, if you wanted to keep it nice and simple! 😀 That'll teach me to look more closely at what the posted code is actually trying to accomplish before posting an unnecessarily complex solution. 😀
November 10, 2011 at 7:33 am
ChrisM@Work (11/10/2011)
This should do it:
SUM(CASE WHEN e.EQUIPMENT_TYPE_CD like '20%' THEN 1 ELSE 2 END* CASE WHEN e.BOOKING_EQ_REQ_DETAIL_ID IS NULL THEN 0 ELSE 1 END) AS BookedTEU
This gives you a 1 for each row where e.BOOKING_EQ_REQ_DETAIL_ID is not null.
Thanks a lot for your solution !!!
November 10, 2011 at 7:34 am
JonFox (11/10/2011)
ChrisM@Work (11/10/2011)
This should do it:
SUM(CASE WHEN e.EQUIPMENT_TYPE_CD like '20%' THEN 1 ELSE 2 END* CASE WHEN e.BOOKING_EQ_REQ_DETAIL_ID IS NULL THEN 0 ELSE 1 END) AS BookedTEU
This gives you a 1 for each row where e.BOOKING_EQ_REQ_DETAIL_ID is not null.
Well, yeah, you COULD do it that way, if you wanted to keep it nice and simple! 😀 That'll teach me to look more closely at what the posted code is actually trying to accomplish before posting an unnecessarily complex solution. 😀
No, I dont agree with you..you did your best based upon what I sent you. Thanks for your help also !! you dont know when your solution could come handy !!
November 10, 2011 at 7:40 am
pwalter83 (11/10/2011)
No, I dont agree with you..you did your best based upon what I sent you. Thanks for your help also !! you dont know when your solution could come handy !!
Derived tables are definitely a useful thing to keep in your toolbox; although not necessary in this situation, there are times when they're definitely needed to solve similar problems. And you're welcome! 🙂
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply