How to Query Data From SQL Server?

Asked 26/12, 2012 at 16:15 Answered 26/12, 2012 at 16:52

Solved sql sql-server vb.net-2010 report-viewer2010

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

Malta answered 26/12, 2012 at 16:15 Comment(3)

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. – Gree 26/12, 2012 at 16:17

Can you show us your expected output too please? – Ebberta 26/12, 2012 at 16:18

@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 ;) – Ebberta 26/12, 2012 at 17:55

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

Dumfound answered 26/12, 2012 at 16:52 Comment(6)

+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 :) – Ebberta 26/12, 2012 at 17:53

@Ebberta 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 – Dumfound 26/12, 2012 at 18:6

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 – Ebberta 26/12, 2012 at 18:22

@Ebberta 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. – Dumfound 26/12, 2012 at 18:23

I wanted to make your answer beautiful so I took liberty to update it with results LOL :D cheers. – Ebberta 26/12, 2012 at 18:29

@Ebberta Thanks :-) (though I edited the perfect results with something a little more my style) – Dumfound 26/12, 2012 at 18:33

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

Ebberta answered 26/12, 2012 at 16:30 Comment(1)

@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. :) – Ebberta 26/12, 2012 at 17:51

EDIT Using CASE

Recommended topics

Hot tags