How to query data from SQL Server?

Question

I have a problem with the request data from the database, to make a statement in VB.NET. I use the business object to create the report. And here is my data:

___________________________________________________________________________ | | | | | | | | Id | Item | Unit | Unit Price | Quantity | Amount | |____|_______________|__________|_____________|___________|_______________| | 1 | Gasoline | L | $ 2.00 | 10 | $ 20.00 | | 1 | Gasoline | L | $ 2.50 | 20 | $ 50.00 | | 2 | Water | Bottle | $ 5.00 | 10 | $ 50.00 | | 3 | Meat | Kg | $ 14.90 | 15 | $ 223.50 | | 1 | Gasoline | L | $ 8.00 | 50 | $ 400.00 | | 4 | Milk | Can | $ 7.45 | 30 | $ 223.50 | | 1 | Gasoline | L | $ 6.99 | 10 | $ 69.90 | |____|_______________|__________|_____________|___________|_______________| 

In the report, I want to see the "Id", "Item", "Unit", "Unit Price" (And yes, I'll show you "Undefined", if they have a different meaning) "count" (the sum of the same subject) and "Amount" (the sum of the same item). But the few times I tried it, the result is wrong. How to calculate the "Amount" is the same element if their "price per unit" - it's not the same price. Here is my expected result:

 ___________________________________________________________________________ | | | | | | | | Id | Item | Unit | Unit Price | Quantity | Amount | |____|_______________|__________|_____________|___________|_______________| | 1 | Gasoline | L | Undefined | 90 | $ 539.90 | | 2 | Water | Bottle | $ 5.00 | 10 | $ 50.00 | | 3 | Meat | Kg | $ 14.90 | 15 | $ 223.50 | | 4 | Milk | Can | $ 7.45 | 30 | $ 223.50 | |____|_______________|__________|_____________|___________|_______________| | ___________ | _______________ | ___________________________________________________________________________ | | | | | | | | Id | Item | Unit | Unit Price | Quantity | Amount | |____|_______________|__________|_____________|___________|_______________| | 1 | Gasoline | L | Undefined | 90 | $ 539.90 | | 2 | Water | Bottle | $ 5.00 | 10 | $ 50.00 | | 3 | Meat | Kg | $ 14.90 | 15 | $ 223.50 | | 4 | Milk | Can | $ 7.45 | 30 | $ 223.50 | |____|_______________|__________|_____________|___________|_______________| 

Please help me....

Answer 1

If you understood correctly, it should do what you want:

 SELECT A.Id, A.Item, A.Unit, CASE WHEN B.Id IS NOT NULL THEN 'Undefined' ELSE [Unit Price] END [Unit Price], A.Quantity, A.Amount FROM ( SELECT Id, Item, Unit, CAST(MIN([Unit Price]) AS VARCHAR(20)) [Unit Price], SUM(Quantity) Quantity, SUM(Amount) Amount FROM YourTable GROUP BY Id, Item, Unit) A LEFT JOIN ( SELECT Id FROM YourTable GROUP BY Id HAVING COUNT(DISTINCT [Unit Price]) > 1) B ON A.Id = B.Id Undefined' ELSE [Unit Price] END [Unit Price], SELECT A.Id, A.Item, A.Unit, CASE WHEN B.Id IS NOT NULL THEN 'Undefined' ELSE [Unit Price] END [Unit Price], A.Quantity, A.Amount FROM ( SELECT Id, Item, Unit, CAST(MIN([Unit Price]) AS VARCHAR(20)) [Unit Price], SUM(Quantity) Quantity, SUM(Amount) Amount FROM YourTable GROUP BY Id, Item, Unit) A LEFT JOIN ( SELECT Id FROM YourTable GROUP BY Id HAVING COUNT(DISTINCT [Unit Price]) > 1) B ON A.Id = B.Id , SELECT A.Id, A.Item, A.Unit, CASE WHEN B.Id IS NOT NULL THEN 'Undefined' ELSE [Unit Price] END [Unit Price], A.Quantity, A.Amount FROM ( SELECT Id, Item, Unit, CAST(MIN([Unit Price]) AS VARCHAR(20)) [Unit Price], SUM(Quantity) Quantity, SUM(Amount) Amount FROM YourTable GROUP BY Id, Item, Unit) A LEFT JOIN ( SELECT Id FROM YourTable GROUP BY Id HAVING COUNT(DISTINCT [Unit Price]) > 1) B ON A.Id = B.Id Amount) Amount SELECT A.Id, A.Item, A.Unit, CASE WHEN B.Id IS NOT NULL THEN 'Undefined' ELSE [Unit Price] END [Unit Price], A.Quantity, A.Amount FROM ( SELECT Id, Item, Unit, CAST(MIN([Unit Price]) AS VARCHAR(20)) [Unit Price], SUM(Quantity) Quantity, SUM(Amount) Amount FROM YourTable GROUP BY Id, Item, Unit) A LEFT JOIN ( SELECT Id FROM YourTable GROUP BY Id HAVING COUNT(DISTINCT [Unit Price]) > 1) B ON A.Id = B.Id A SELECT A.Id, A.Item, A.Unit, CASE WHEN B.Id IS NOT NULL THEN 'Undefined' ELSE [Unit Price] END [Unit Price], A.Quantity, A.Amount FROM ( SELECT Id, Item, Unit, CAST(MIN([Unit Price]) AS VARCHAR(20)) [Unit Price], SUM(Quantity) Quantity, SUM(Amount) Amount FROM YourTable GROUP BY Id, Item, Unit) A LEFT JOIN ( SELECT Id FROM YourTable GROUP BY Id HAVING COUNT(DISTINCT [Unit Price]) > 1) B ON A.Id = B.Id )> SELECT A.Id, A.Item, A.Unit, CASE WHEN B.Id IS NOT NULL THEN 'Undefined' ELSE [Unit Price] END [Unit Price], A.Quantity, A.Amount FROM ( SELECT Id, Item, Unit, CAST(MIN([Unit Price]) AS VARCHAR(20)) [Unit Price], SUM(Quantity) Quantity, SUM(Amount) Amount FROM YourTable GROUP BY Id, Item, Unit) A LEFT JOIN ( SELECT Id FROM YourTable GROUP BY Id HAVING COUNT(DISTINCT [Unit Price]) > 1) B ON A.Id = B.Id 

Added sql script for you. (Credit @bonCodigo, since I based his violin on the one he already had, but with my code).

This is the result:

 ID ITEM UNIT PRICE QUANTITY AMOUNT 1 Gasoline L Undefined 90 539.9 2 Water Bottle 5.00 20 99.9 3 Meat Kg 14.90 15 223.5 4 Milk Can 7.45 30 223.5 

Answer 2

Try this code, please

 select Id, item, unit, sum(quantity) totoal_Qt , sum(amount) total_Px from td group by id, item, unit ; 

Results:

 ID ITEM UNIT TOTOAL_QT TOTAL_PX 1 Gasoline L 90 539.9 2 Water Bottle 10 50 3 Meat Kg 15 223.5 4 Milk Can 30 223.5 

SQLFIDDLE

---

EDIT Using CASE

This is the closest I could get. That's a good question. +1 for that. Thus, with this lack of the edited response to the fact that he will show you the "Undefined" for a single item with multiple unit_prices , but he did not show unit_price, even in one element multipel entries have the same unit_price . Response is fully consistent with the syntax of ANSI .

* SQLFIDDLE

Changed the sample data to test different scenarios.

 ID ITEM UNIT UNIT_PRICE QUANTITY AMOUNT 1 Gasoline L 2 10 20 1 Gasoline L 2.5 20 50 2 Water Bottle 5 10 50 3 Meat Kg 14.9 15 223.5 1 Gasoline L 8 50 400 4 Milk Can 7.45 30 223.5 1 Gasoline L 6.99 10 69.9 2 Water Bottle 5 10 49.9 

Query:

 select distinct x.id, x.item, x.unit, x.total_Qt, x.total_Amt, case when x.unitPrice = 0 then 'Undefined' else cast(y.unit_price as varchar(9)) end as UP from( select Id, item, unit, sum(quantity) total_Qt , sum(amount) total_Amt, case when count(unit_price)>1 then 0 else 1 end unitPrice from td group by id, item, unit) as x left join (select distinct id, item, unit, unit_price from td) as y on x.id = y.id and x.item = y.item and x.unit = y.unit ; (quantity) total_Qt, select distinct x.id, x.item, x.unit, x.total_Qt, x.total_Amt, case when x.unitPrice = 0 then 'Undefined' else cast(y.unit_price as varchar(9)) end as UP from( select Id, item, unit, sum(quantity) total_Qt , sum(amount) total_Amt, case when count(unit_price)>1 then 0 else 1 end unitPrice from td group by id, item, unit) as x left join (select distinct id, item, unit, unit_price from td) as y on x.id = y.id and x.item = y.item and x.unit = y.unit ; , unit_price select distinct x.id, x.item, x.unit, x.total_Qt, x.total_Amt, case when x.unitPrice = 0 then 'Undefined' else cast(y.unit_price as varchar(9)) end as UP from( select Id, item, unit, sum(quantity) total_Qt , sum(amount) total_Amt, case when count(unit_price)>1 then 0 else 1 end unitPrice from td group by id, item, unit) as x left join (select distinct id, item, unit, unit_price from td) as y on x.id = y.id and x.item = y.item and x.unit = y.unit ; 

Results:

 ID ITEM UNIT TOTAL_QT TOTAL_AMT UP 1 Gasoline L 90 539.9 Undefined 2 Water Bottle 20 99.9 Undefined 3 Meat Kg 15 223.5 14.90 4 Milk Can 30 223.5 7.45