Custom Select with SQL
My database records sample as below
I am getting results as below
SELECT
Category,
SubCategory,
COUNT(Category) AS [CategoryCount],
CASE
WHEN Category = 'Vegetables' THEN 3.1
WHEN Category = 'Animals' THEN 4.1
WHEN Category = 'Cars' THEN 3.1
WHEN Category = 'Fruits' THEN 2.7
ELSE 0 END AS [OrderRank]
FROM
Table1
GROUP BY
Category
ORDER BY
OrderRank DESC,
Category
I would like to get results as below, I mean if grouped Category and SubCatgory rows records more than 1 Category row must be "Category" but grouped Category and SubCategory equal 1 "SubCategory" row must be blank or NULL
SELECT
Category,
CASE
WHEN COUNT(DISTINCT SubCategory) > 1 THEN ''
ELSE MAX(SubCategory)
END,
COUNT(Category) AS [CategoryCount],
CASE
WHEN Category = 'Vegetables' THEN 5.3
WHEN Category = 'Animals' THEN 4.1
WHEN Category = 'Cars' THEN 3.1
WHEN Category = 'Fruits' THEN 2.7
ELSE 0 END AS [OrderRank]
FROM
Table1
GROUP BY
Category
ORDER BY
OrderRank DESC,
Category
Give this a shot.
Using a case statement to check your count you can either supply the subCategory or blank.
EDIT: Answer change after Kerebos updated question.
Running the code I have posted above generates the following result set:
I have updated the COUNT() case statement to check for distinct subcategories. It isn't the prettiest solution but it works
Besides the fact your Orderrank is different (which is because you have run code that is different to what you have posted because they are hard coded values) the code generates the expected result set. If the ordering is the issue then just change the ordering.
EDIT2: I've just updated my code to generate the correct ordering as per your question.