How to list posts from one table and at the same time sum an amount from another table?
First of all, hope my English isn't too bad..
I have a problem I can't solve. The thing is that I am thinking about an order handling system.. What I have so far is a table where every article registrats that belong to an order like this
Tbl_orderLines id, prod_week, article_id
Then i also have och table where the amount for each row in tbl_orde开发者_JAVA技巧rLines registrates like this
Tbl_inventory orderline_id, article_id, amount
Now article_id in both tables are the same (double post) thought it would be easier to do so? What I want to is the get the available amount of each specific article at a specific time. So I want to be able to list every row in Tbl_orderLInes and with a join get the amount for every row, but! I also want to get the total amount of a specific article. Therefore, I want to sum the amount column from Tbl_inventory where the article_id is the same as the row from tbl_orderLines.
This is what I've got now Tbl_orderLines
id prod_week article_id
1 1130 2
2 1129 5
3 1129 2
4 1128 2
Tbl_inventory
orderLine_id article_id amount
1 2 1
2 5 2
3 2 1
4 2 2
The result should be something like
id prod_week article_id amount TotalAmount
1 1130 2 1 4
2 1129 5 2 2
3 1129 2 1 3
4 1128 2 2 2
Don't know if its possible to understand my question? I'm using MSSQL..
Much appreciate any help..
I believe the first part of your result set is achieve by the following.
SELECT
[order_line].id,
[order_line].prod_week,
[order_line].article_id,
[inventory].amount
FROM
Tbl_orderLines AS [order_line]
INNER JOIN
Tbl_inventory AS [inventory]
ON [inventory].orderline_id = [order_line].id
I'm not 100% certain about the TotalAmount column, however. My best guess is that you want...The sum of the amounts for all preceeding weeks with the same article_id?
SELECT
[order_line].id,
[order_line].prod_week,
[order_line].article_id,
[inventory].amount,
SUM([historic_inventory].amount) AS TotalAmount
FROM
Tbl_orderLines AS [order_line]
INNER JOIN
Tbl_inventory AS [inventory]
ON [inventory].orderline_id = [order_line].id
INNER JOIN
Tbl_orderLines AS [historic_orders]
ON [historic_orders].article_id = [order_line].article_id
AND[historic_orders].prod_week <= [order_line].prod_week
INNER JOIN
Tbl_inventory AS [historic_inventory]
ON [historic_inventory].orderline_id = [historic_order_line].id
GROUP BY
[order_line].id,
[order_line].prod_week,
[order_line].article_id,
[inventory].amount
I can't get exactly what you want but I believe you can try something like this enhance the idea towards your goal.
CREATE TABLE [dbo].[inventory](
[id] [int] NULL,
[article_id] [int] NULL,
[amount] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[orderLines](
[id] [int] NULL,
[prod_week] [int] NULL,
[article_id] [int] NULL
) ON [PRIMARY]
GO
SELECT ol.id,
ol.prod_week,
ol.article_id,
i.amount,
SUM(amount) OVER (PARTITION BY i.article_id) AS totalAmount
FROM dbo.orderLines AS ol, dbo.inventory AS i
WHERE ol.id = i.id
ORDER BY ol.id
精彩评论