Without using \'GROUP BY\' clause in any SELECT statements, write a Transact-SQL

Question

Without using 'GROUP BY' clause in any SELECT statements, write a Transact-SQL script in the space below between lines of 'use' and 'go' to generate the same two-column output of 29 rows (one for each SupplierID in Products based on the original ClearWater database) as the DML statement below.

SELECT SupplierID, COUNT(*) AS TotalProducts FROM ClearWater.dbo.Products GROUP BY SupplierID;

Since data of the Products table can be changed any time, your script should not assume the table contains 29 SupplierIDs. That is, the number 29 cannot appear in your script. The only constant value allowed here is 1.

Hint 1: same as in Problem 1, think and write down a step-by-step logical but manual procedure to simulate GROUP BY before coding your solution

Hint 2: think and design a step-by-step logical procedure to simulate before coding

Hint 3: since the output contains rows and columns, you could use a table Variable or a temporary table, and use INSERT and UPDATE to manipulate it

Hint 4: use WHILE, IF-ELSE like in Problem 1

Hint 5: use one or two standard SELECT statements to get the total number of products and total number of different SupplierIDs in Products table.

Hint 6: this could possibly be solved by 14~16 lines, not including 'use' and 'go'.*/use Clearwater;go

Explanation / Answer

USE;

SET NOCOUNT ON;  

DECLARE @supplier_id int;

IF EXISTS DROP TABLE tmp.tmpProduct

SELECT DISTINCT SupplierID

INTO tmp.tmpProduct

FROM ClearWater.dbo.Products

ALTER TABLE tmp.tmpProduct

ADD COLUMN countOfSupplier DEFAULT 0

DECLARE supplier_cursor CURSOR FOR

SELECT SupplierID

FROM ClearWater.dbo.Products

OPEN supplier_cursor  

FETCH NEXT FROM supplier_cursor

INTO @supplier_id  

WHILE @@FETCH_STATUS = 0  

BEGIN  

UPDATE tmp.tmpProduct

SET countOfSupplier = countOfSupplier + 1

WHERE SupplierID = @supplier_id

FETCH NEXT FROM supplier_cursor

INTO @supplier_id

END

CLOSE supplier_cursor;  

DEALLOCATE supplier_cursor;  

GO;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.