Kerberos Kerberos - 3 years ago 104
SQL Question

Custom Select with SQL

My database records sample as below

Database Records

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


SQL Results

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

enter image description here

Answer Source
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:

enter image description here

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download