April 23, 2015 at 9:53 am
I'm trying to get a count of the l27_mfyp_id based on the below condition in my case statement. What am I doing wrong here?
case
when (l27_mfyp_crs_rank = '1') then count(l27_mfyp_id) over (partition by l27_mfyp_crs_id) as "RANK_1"
April 23, 2015 at 10:15 am
Would probably help if you posted the entire query not just the snippet above.
You should also read the first article I reference below in my signature block regarding asking for help. It will walk you through the what and how of things to post to get the best possible answers. What it comes down to is more information helps us help you better.
April 23, 2015 at 10:28 am
Ok...so, basically I am trying to count the id's by course_id using the over partition by function.
select distinct l27_mfyp_crs_id "CRS_ID", l27_mfyp_crs_pair1 "COURSE_1" , L27_MFYP_CRS_PAIR1_DESC "COURSE_1_TITLE",
l27_mfyp_crs_pair2 "COURSE_2", L27_MFYP_CRS_PAIR2_DESC "COURSE_2_TITLE",
case
when (l27_mfyp_crs_rank = '1') then count(l27_mfyp_id) over (partition by l27_mfyp_crs_id) as "RANK_1",
count(*) over (partition by l27_mfyp_crs_id) as "TOTAL_ALL_RANKS"
from COURSES
WHERE l27_mfyp_crs_id is not NULL
The error message is: Incorrect syntax near the keyword 'as'.....I also tried moving the count on the outside of the case statement.
April 23, 2015 at 10:53 am
Going to take a stab in the dark.
I think you probably should do the ranking all by itself inside a windowed query and then do the case statement on the outer query like the example below that I swiped and altered herehttp://www.sqlservercentral.com/blogs/practicalsqldba/2012/09/10/sql-server-usage-of-over-clause/
I don't think you can do a over clause as part of a case statement, but I am not a pro.
Might be worth giving the URL a read.
SELECTSalesOrderID, ProductName, CASEWHEN TotalOrderQty > 10 * 'Avg Qty of Item' THEN 'Avg Qty of Item'
END AS myMadeUpColumn
FROM( SELECTSalesOrderID, p.Name AS ProductName, OrderQty,
SUM(OrderQty) OVER ( PARTITION BY SalesOrderID ) AS TotalOrderQty,
AVG(OrderQty) OVER ( PARTITION BY SalesOrderID ) AS 'Avg Qty of Item',
COUNT(OrderQty) OVER ( PARTITION BY SalesOrderID ) AS 'Total Number of Item',
MIN(OrderQty) OVER ( PARTITION BY SalesOrderID ) AS 'Min order Qty',
MAX(OrderQty) OVER ( PARTITION BY SalesOrderID ) AS 'Max Order Qty'
FROMSales.SalesOrderDetail SOD
INNER JOIN Production.Product p ON SOD.ProductID = p.ProductID
) window
WHERE'Min order Qty'> 1
April 23, 2015 at 2:43 pm
The syntax error is that the CASE function (not a statement in T-SQL) is missing the END keyword.
April 23, 2015 at 2:46 pm
thanks for all the help guys.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply