How to Query Data From SQL Server?

Question

I have a problem in query data from database to make report in VB.NET. I use the Business Object to do the report. And here is my example 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 report, I want to see the "Id" , "Item" , "Unit" , "Unit Price" (And yes, this one I will show "Undefined" instead if they have the different value), "Quantity" (Sum of the same item) and "Amount" (Sum of the same item). But I have tried a few times, the result is wrong. How to calculate the "Amount" all the same item, if their "Unit Price" are not the same price at all. 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    |
|____|_______________|__________|_____________|___________|_______________|

Please help me....

Answer 1

If I understood you correctly, this 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

Added an sql fiddle for you to try. (Credit to @bonCodigo, since I based my fiddle 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. It is a nice question. +1 for that. So the flaw with this editted answer that it will show you 'Undefined' for one item that has multiple unit_prices, however it will not show the unit_price even one items's multipel entries have the same unit_price. Answer is in full ANSI syntax.

*SQLFIDDLE

Changed the sample data to test for various 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
;

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