January 22, 2019 at 12:36 pm
I need to count the SOLD as an aggregate. I am using the CASE Statement to create the count as it a date field, so I need to count using the case statement.
SELECT
AU.AUCTION_COMPANY,
A.AUTOIMS_CURR_AUCT_NM,
V.VIN_NBR,
COUNT(L.SCHED_SALE_DT) AS OFFERED,
CASE
WHEN L.SOLD_DT LIKE '1900%'
THEN '0'
ELSE '1'
END AS SOLD,
A.SALE_PRICE_AMT,
A.RETENTION_PRICE AS MMR,
A.CLIENT_ID_CODE AS CLIENT
FROM
ADMIN.V_REMARKT_SALE_EVENT_LIFECYCLE_FACT L
LEFT JOIN ADMIN.V_REMARKT_VEHICLE_DIM V ON V.VEHICLE_DIM_ID = L.VEHICLE_DIM_ID
LEFT JOIN ADMIN.V_REMARKT_AUTOIMS_DAILY_FACT A ON A.VEHICLE_DIM_ID = L.VEHICLE_DIM_ID
JOIN ADMIN.V_REMARKT_AUCTION_DIM AU ON AU.AUCTION_DIM_ID = A.AUCTION_DIM_ID
January 22, 2019 at 1:32 pm
I think you will also need a GROUP BY clause in your query. To handle the conditional count, the easiest way is to use SUM instead:COUNT(L.SCHED_SALE_DT) AS OFFERED,
SUM(CASE WHEN L.SOLD_DT LIKE '1900%' THEN 0 ELSE 1 END) AS SOLD,
January 22, 2019 at 1:38 pm
Chris Harshman - Tuesday, January 22, 2019 1:32 PMI think you will also need a GROUP BY clause in your query. To handle the conditional count, the easiest way is to use SUM instead:COUNT(L.SCHED_SALE_DT) AS OFFERED,
SUM(CASE WHEN L.SOLD_DT LIKE '1900%' THEN 0 ELSE 1 END) AS SOLD,
Thank you, I did not put the group by in when I posted this, that is a given, lol. I will try the sum, ty
January 22, 2019 at 1:53 pm
thomas.miller1 - Tuesday, January 22, 2019 1:38 PMChris Harshman - Tuesday, January 22, 2019 1:32 PMI think you will also need a GROUP BY clause in your query. To handle the conditional count, the easiest way is to use SUM instead:COUNT(L.SCHED_SALE_DT) AS OFFERED,
SUM(CASE WHEN L.SOLD_DT LIKE '1900%' THEN 0 ELSE 1 END) AS SOLD,Thank you, I did not put the group by in when I posted this, that is a given, lol. I will try the sum, ty
An alternative would be to use the COUNT function directly by understanding how aggregate functions handle NULLs.COUNT(CASE WHEN L.SOLD_DT NOT LIKE '1900%' THEN 1 END) AS SOLD
This is assuming SOLD_DT can't be NULL.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply