CODEWITHSUNDEEP
sqlsql-servergroupingrollup
chandra
chandra

Reputation: 13

How to display products under Category in sql in a table

I have the following table:

product

where the products are in different categories and i am excepting the output:

output

like product and its cost need to be displayed under category(For category cost value i want to display total products cost) .I tried with different approaches by using roll up and grouping , but i am not getting excepted output.

Upvotes: 0

Views: 6463

Answers (3)

Fuzzy
Fuzzy

Reputation: 3810

Here it goes:

Sample Data:

CREATE TABLE #product (ID INT, Category VARCHAR(50), Product VARCHAR(50), Value INT)
INSERT INTO #product
VALUES(1,'Non-veg','Chicken',150),
(2,'Non-veg','Mutton',200),
(3,'Non-veg','Fish',220),
(4,'Non-veg','Prawns',250),
(5,'Veg','Gobi',100),
(6,'Veg','Parota',45),
(7,'Veg','vegbirani',150)

Query using GROUP BY with ROLLUP

SELECT  Category, Product,
       SUM(Value) AS Value
FROM #product
GROUP BY Category, Product WITH ROLLUP

Results:

enter image description here

you can further manipulate the results:

SELECT  COALESCE(product,category,'Total') Category,
       SUM(Value) AS Value
FROM #product
GROUP BY Category, Product WITH ROLLUP

Result:

enter image description here

To answer the comment below: "is there any way to display Category first then Products" this seemed to work:

;WITH CTE AS (
SELECT  Category, Product,
       SUM(Value) AS Value,
      ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Product  ) AS rn
FROM #product
GROUP BY Category, Product WITH ROLLUP)

SELECT  Category = COALESCE(A.product,A.category,'Total') , A.Value 
FROM CTE AS A 
ORDER BY ISNULL(A.category,'zzzzzz') ,rn

Results:

enter image description here

Upvotes: 2

xQbert
xQbert

Reputation: 35333

Maybe something like this... doesn't give your exact output but it's close...

Select category, product, sum(value) as value
From TableName
group by grouping sets ((category),(category, product))

Upvotes: 0

JamieD77
JamieD77

Reputation: 13949

Using Rollup you would do it like this.

SELECT  COALESCE(product,category,'Total') Category,
        SUM(VALUE) cost
FROM    products
GROUP BY ROLLUP(category,product)

Upvotes: 3

Related Questions