SQL Advice - Aggregating data for specific cases

Essentially I need to group unique product data into a single row when:

• The supplier sku matches
• The price for the products match (or) 1 of the product lines is equal to '0.00'

Here is a sample dataset set along with a working query for what I'm trying to accomplish. I'm simply not entirely comfortable that this is the best way to perform this query.

DECLARE @Test TABLE
(
SupplierSKU VARCHAR(25),
Description VARCHAR(50),
Quantity VARCHAR(25),
Price VARCHAR(25)
)

INSERT INTO @Test
SELECT '123', 'APPLES', '15', '0.00'
INSERT INTO @Test
SELECT '124', 'ORANGES', '10', '15.34'
INSERT INTO @Test
SELECT '123', 'APPLES', '5', '27.40'
INSERT INTO @Test
SELECT '125', 'PLUMS', '67', '34.86'
INSERT INTO @Test
SELECT '124', 'ORANGES', '10', '15.78'
INSERT INTO @Test
SELECT '125', 'PLUMS', '3', '34.86'


SELECT SupplierSKU, Description, SUM(Quantity) AS [Quantity], MAX(Price) AS [Price]
FROM
(
    SELECT SupplierSKU, Description, SUM(CAST(Quantity AS INT)) AS [Quantity], (SELECT MAX(CAST(Price AS MONEY)) AS [Price] FROM @Test ti WHERE ti.SupplierSKU = t.SupplierSKU AND ti.Price开发者_Go百科 = t.price AND ti.Price <> '0.00') AS [Price]
    FROM @Test t
    GROUP BY SupplierSKU, Description, Price
) pdata
GROUP BY pdata.SupplierSKU, pdata.Description

The desired results:

SupplierSKU Description Quantity    Price 
123         APPLES          20      27.40 
124         ORANGES         10      15.34
124         ORANGES         10      15.78  
125         PLUMS           70      34.86

---

This should produce the "desired output" but the desired output is not consistent with the textual objective. Why are quantity and price varchar. This solution assumes you convert quantity to integer on the SQL table.

    SELECT SupplierSKU, Description, SUM(Quantity), P AS [Price]
    FROM @test 
    Where [Price] > 0 
    GROUP BY SupplierSKU, Description, Price

---

First I repaired your own solution

SELECT SupplierSKU, Description, SUM(Quantity) AS [Quantity], MAX(cast(Price as money)) AS [Price] 
FROM 
( 
    SELECT SupplierSKU, Description, SUM(CAST(Quantity AS INT)) AS [Quantity], 
    (SELECT MAX(cast(PRICE as money)) from @test 
    where t.SupplierSKU = SupplierSKU and Description = t.Description and (t.price = '0.00' or t.price = price)) price
    FROM @Test t 
    GROUP BY SupplierSKU, Description , price
) pdata 
GROUP BY pdata.SupplierSKU, pdata.Description , price

Then I rewrote your solution to something more readable

SELECT suppliersku, description, sum(cast(quantity as int)) quantity, max(cast(price as money)) price FROM (
SELECT suppliersku, description, quantity, price FROM @test
WHERE price <> '0.00'
UNION ALL 
SELECT t1.suppliersku, t1.description, t1.quantity, max(t2.price)
FROM @test t1
join
@test t2 ON t1.SupplierSKU=t2.SupplierSKU and t1.Description = t2.Description
WHERE t1.price = '0.00'
GROUP BY t1.suppliersku, t1.description, t1.quantity
) a
GROUP BY suppliersku, description, price

You may notice i get the right quantity of apples (15+5 = 20)

---

SELECT SupplierSKU, Description, SUM(Quantity) AS Qte, SUM(Price) AS Total
FROM @test t
GROUP BY SupplierSKU, Description
ORDER BY SupplierSKU

Does that gets you what you want?