group by and sum

Hi,
I have a table like this;
WAREHOUSE_STOCKS
--
WAREHOUSE_NAME (varchar)
ITEM_NAME (varchar)
LOT_NAME (varchar)
QUANTITY (int)
and these are records,
| WAREHOUSE_NAME | ITEM_NAME | LOT_NAME | QUANTITY |
Wr-1, It-1, Lt-1, 10
Wr-1, It-1, Lt-2, 20
Wr-1, It-2, Lt-1, 10
Wr-2, It-1, Lt-1, 5
Wr-2, It-1, Lt-2, 20
Wr-2, It-1, Lt-3, 10
I want to write SQL and show sum(quantity) "group by" warehouse and item,
like this;
| WAREHOUSE_NAME | ITEM_NAME | QUANTITY |
Wr-1, It-1, 30
Wr-1, It-2, 10
Wr-2, It-1, 35
How can I do?
thanksSeems to be a simple GROUP BY with aggregate function:
SELECT warehouse_name, item_name, SUM(quantity) AS quantity
FROM warehouse_stock
GROUP BY warehouse_stock, item_name
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yunus Efe" <Yunus Efe@.discussions.microsoft.com> wrote in message
news:5D27FE26-FACE-476C-B639-B170A894383E@.microsoft.com...
> Hi,
> I have a table like this;
> WAREHOUSE_STOCKS
> --
> WAREHOUSE_NAME (varchar)
> ITEM_NAME (varchar)
> LOT_NAME (varchar)
> QUANTITY (int)
> and these are records,
> | WAREHOUSE_NAME | ITEM_NAME | LOT_NAME | QUANTITY |
> Wr-1, It-1, Lt-1, 10
> Wr-1, It-1, Lt-2, 20
> Wr-1, It-2, Lt-1, 10
> Wr-2, It-1, Lt-1, 5
> Wr-2, It-1, Lt-2, 20
> Wr-2, It-1, Lt-3, 10
> I want to write SQL and show sum(quantity) "group by" warehouse and item,
> like this;
> | WAREHOUSE_NAME | ITEM_NAME | QUANTITY |
> Wr-1, It-1, 30
> Wr-1, It-2, 10
> Wr-2, It-1, 35
> How can I do?
> thanks
>|||Try,
select WAREHOUSE_NAME, ITEM_NAME, sum(QUANTITY) as sum_QUANTITY
from dbo.WAREHOUSE_STOCKS
group by WAREHOUSE_NAME, ITEM_NAME
See "select statement" in BOL.
AMB
"Yunus Efe" wrote:

> Hi,
> I have a table like this;
> WAREHOUSE_STOCKS
> --
> WAREHOUSE_NAME (varchar)
> ITEM_NAME (varchar)
> LOT_NAME (varchar)
> QUANTITY (int)
> and these are records,
> | WAREHOUSE_NAME | ITEM_NAME | LOT_NAME | QUANTITY |
> Wr-1, It-1, Lt-1, 10
> Wr-1, It-1, Lt-2, 20
> Wr-1, It-2, Lt-1, 10
> Wr-2, It-1, Lt-1, 5
> Wr-2, It-1, Lt-2, 20
> Wr-2, It-1, Lt-3, 10
> I want to write SQL and show sum(quantity) "group by" warehouse and item,
> like this;
> | WAREHOUSE_NAME | ITEM_NAME | QUANTITY |
> Wr-1, It-1, 30
> Wr-1, It-2, 10
> Wr-2, It-1, 35
> How can I do?
> thanks
>|||select WAREHOUSE_NAME , ITEM_NAME, sum(QUANTITY )
from WAREHOUSE_STOCKS
group by WAREHOUSE_NAME , ITEM_NAME
"Yunus Efe" <Yunus Efe@.discussions.microsoft.com> wrote in message
news:5D27FE26-FACE-476C-B639-B170A894383E@.microsoft.com...
> Hi,
> I have a table like this;
> WAREHOUSE_STOCKS
> --
> WAREHOUSE_NAME (varchar)
> ITEM_NAME (varchar)
> LOT_NAME (varchar)
> QUANTITY (int)
> and these are records,
> | WAREHOUSE_NAME | ITEM_NAME | LOT_NAME | QUANTITY |
> Wr-1, It-1, Lt-1, 10
> Wr-1, It-1, Lt-2, 20
> Wr-1, It-2, Lt-1, 10
> Wr-2, It-1, Lt-1, 5
> Wr-2, It-1, Lt-2, 20
> Wr-2, It-1, Lt-3, 10
> I want to write SQL and show sum(quantity) "group by" warehouse and item,
> like this;
> | WAREHOUSE_NAME | ITEM_NAME | QUANTITY |
> Wr-1, It-1, 30
> Wr-1, It-2, 10
> Wr-2, It-1, 35
> How can I do?
> thanks
>