crossTab Sum - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

crossTab Sum

Author

Topic

hillng
Starting Member

5 Posts

Posted - 2004-06-01 : 04:46:28

two table: one is order info, another is delivered info
structure like this:
order: [order] [productid] [order qty]
1, 123, 400
1, 289, 100
....

delivered: [order] [productid] [delivered qty] [deliver date]
1, 123, 250, day1
1, 289, 100, day1
1, 123, 150, day2
......

a query that get order qty and delivered qty result like this
[product] [orderqty] [delivered qty]
123, 400, 250
123, 400, 150

I try to get sum of the record like
[order] [orderqty] [delivered qty]
1, 400, 400

--------------
select
sum(pd.qty) as ordered,
sum(map.qty) as mapped,
from po_detail pd
left join
goods_list_detail_mapping map
on po.po_number=map.po_no and pd.id=map.product_id

where pd.po_number=1

--------------------------------
but result is 123, 800, 400

Anyone can help, Thanks

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-06-01 : 04:58:13

You need to group by that column, since you want to total only the "delivered quantity" column:


select pd.product_id
pd.qty as ordered,
sum(map.qty) as mapped,
from po_detail pd
left join
goods_list_detail_mapping map
on po.po_number=map.po_no and pd.id=map.product_id
group by pd.product_id, pd.qty

hillng
Starting Member

5 Posts

Posted - 2004-06-01 : 05:05:43

Thanks for reply!
Maybe I didn't make it clear, Sum(pd.qty) is needed as not only one product in an order, like product1: 400, product2:500..., each product qty point to 1 or more mapped qty.

hillng
Starting Member

5 Posts

Posted - 2004-06-01 : 05:09:26

so record should like:

order: [order] [productid] [order qty]
1, 123, 400
1, 289, 100
....

delivered: [order] [productid] [delivered qty] [deliver date]
1, 123, 250, day1
1, 289, 100, day1
1, 123, 150, day2

result:
[ordered qty] [delivered qty]
500, 500

but I only got:
[order] [ordered qty] [delivered qty]
1, 900, 500
2, ....

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-06-01 : 05:55:22

Ok, I see it now. There are two ways you can do this, use a subquery or join twice using derived tables. Using a derived table:


SELECT orderid, ordered_qty, delivered_qty
FROM
(SELECT orderid, SUM(order_qty) AS ordered_qty FROM orders GROUP BY orderid) A
INNER JOIN 
(SELECT orderid, SUM(delivery_qty) AS delivered_qty FROM delivery GROUP BY orderid) B
ON A.orderid = b.orderid

Substitute the appropriate column and table names in the above query.

OS

gates_micro
Starting Member

29 Posts

Posted - 2004-06-01 : 06:44:18

This query should work.

SELECT t1.ORDERID,t1.PRODUCTID,t1.ORDERQTY,t2.DELQTY
FROM (SELECT ORDERID,PRODUCTID,SUM(ORDERQTY) AS ORDERQTY FROM ORDERTABLE GROUP BY ORDERID,PRODUCTID)t1,
(SELECT orderid,productid,SUM(DELIVEREDQTY) as DELQTY FROM DELIVERED GROUP BY ORDERID,PRODUCTID)t2 where t1.orderid=t2.orderid and t1.productid=t2.productid
and t1.productid=123

gates_micro
Starting Member

29 Posts

Posted - 2004-06-01 : 06:57:26

The query written before included productid but I assume u don't need it.

So,this one will work.

SELECT t1.ORDERID,t1.ORDERQTY,t2.DELQTY
FROM (SELECT distinct ORDERID,SUM(ORDERQTY) AS ORDERQTY FROM ORDERTABLE GROUP BY ORDERID)t1,
(SELECT orderid,SUM(DELIVEREDQTY) as DELQTY FROM DELIVERED GROUP BY ORDERID)t2
where t1.orderid=t2.orderid

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-06-01 : 07:56:38

Is it just me or do I see echoes here?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources