Insert Multiple Records with One Query and Get Count on the basis of GroupBY +SQL

SQL SERVER 2005

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.


DECLARE@ProductDtls  TABLE(id INT, Department VARCHAR(30),Product VARCHAR(100))

--INSERT MULTIPLE RECORDS WITH ONE QUERY
INSERT INTO @ProductDtls (ID, Department, Product)
SELECT 1,'Electronics', 'Cameras & Video'UNION ALL
SELECT 2,'Electronics','Cameras & Video'UNION ALL
SELECT 3,'Electronics','Cell Phones & Accessories' UNION ALL
SELECT 4,'Electronics','Cell Phones & Accessories' UNION ALL
SELECT 5,'Electronics','Cell Phones & Accessories' UNION ALL
SELECT 6,'Electronics','Computers, Tablets & Laptops' UNION ALL
SELECT 7,'Electronics','Computers, Tablets & Laptops' UNION ALL
SELECT 8,'Fashion','Clothing & Accessories'UNION ALL
SELECT 9,'Fashion','Clothing & Accessories'UNION ALL
SELECT 10,'Fashion','Shoes' UNION ALL       
SELECT 11,'Fashion','Shoes' UNION ALL
SELECT 10,'Collectibles & Art','Antiques' UNION ALL
SELECT 11,'Collectibles & Art','Antiques' UNION ALL
SELECT 10,'Collectibles & Art','Antiques' UNION ALL
SELECT 11,'Collectibles & Art','Art' UNION ALL
SELECT 12,'Collectibles & Art','Art'

--SELECT ALL RECORDS
SELECT * FROM @ProductDtls

--GET COUNT ON THE BASIS OF GROUP RECORDS
SELECT Department, COUNT(T.Product) 'ProductCount' FROM
(SELECT DISTINCT Department,Product FROM @ProductDtls) T
GROUP BY Department

Comments