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....

Can you show the code you're using that isn't working? It looks like `Amount` is just `Unit Price * Quantity`. This could be done in a database view, in a SQL statement, in a business object, in a UI view, etc. — David, Dec 26 '12 at 16:17
@kimleng What if you have two entries from an item that has the same `unit_price`? I believe you want to show the correct unit_price in that case ;) — bonCodigo, Dec 26 '12 at 17:55

Lamak · Accepted Answer · 2012-12-26T18:32:36.637

6

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

edited Dec 26 '12 at 18:32

answered Dec 26 '12 at 16:52

Lamak

69,480
12
108
116

+1 for the answer. Does this support when one item has two entries with the same `unit_price`? Coz I only managed to provide the answer that shows `undefined` if there multiple entries by one item regardless of the unit_price being same or different :) – bonCodigo Dec 26 '12 at 17:53
@bonCodigo Yes, it does take it into account. That's why there is a `LEFT JOIN` on a derived table that uses `COUNT(DISTINCT`, and the `CASE` on the `SELECT` – Lamak Dec 26 '12 at 18:06
Thanks btw, you may need to include `UNIT` in the `group by` clause ;) I was just about to add SQLFIDDLe to your answer with edit to the group by :D – bonCodigo Dec 26 '12 at 18:22
@bonCodigo I added a sqlfiddle based on the one you had, but with my code if you also want to try the results. And I already had added the `Unit` column on my answer, thanks. – Lamak Dec 26 '12 at 18:23
I wanted to make your answer *beautiful* so I took liberty to update it with results LOL :D cheers. – bonCodigo Dec 26 '12 at 18:29
@bonCodigo Thanks :-) (though I edited the *perfect results* with something a little more my style) – Lamak Dec 26 '12 at 18:33

bonCodigo · Answer 2 · 2012-12-26T17:51:03.373

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

@kimleng it's a good question. I have updated the answer to come littler close to what you need. But it has a flaw. Please read on the udpate and comment. :) — bonCodigo, Dec 26 '12 at 17:51

How to Query Data From SQL Server?

2 Answers2

EDIT Using CASE

Linked