how do I group detail using SELECT ??? * RESOLVED*

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)

how do I group detail using SELECT ??? * RESOLVED*

Author

Topic

ferche
Starting Member

2 Posts

Posted - 2009-12-29 : 13:04:18

Below is my current SELECT statement with output. How would I write the SELECT statement so it totals the detail into 1 output line. For example, the detail in red would be 1 line with the QTY field total = 1134. It would total when the so_no & line_no changes.

SELECT so_no, customer_po_no,line_no,facility,facility,shipped_date,facility,customer_part_no,item_no,item_description,facility,item_uom,weight_uom,package_qty_per,qty
FROM SHP_CONTAINER_DETAIL,S_O_HEADER
WHERE so_no = s_o_no
AND customer_no = '1214'
AND customer_po_no LIKE '1%'
AND shipped = '1'
AND shipped_date >= '12/16/2009 0:00:00 AM'
AND shipped_date <= '12/16/2009 11:59:59 PM'

so_no customer_po_no line_no facility facility_1 shipped_date facility_2 customer_part_no item_no item_description facility_3 item_uom weight_uom package_qty_per qty

165447 100074411 36 12/16/2009 W643/OAK 100215|01 F643 01 CRWBL EA EA 1 48
165447 100074411 47 12/16/2009 WM936/BIRCH 103696|01 F306 06 STOP 37 EA EA 1 50
165447 100074411 44 12/16/2009 WM887/OAK 103666|01 F305 01 STOP 37 EA EA 1 50
165447 100074411 27 12/16/2009 WM356H/OAK 107311|01 F1072 01 CSG 44 EA EA 1 50
165447 100074411 23 12/16/2009 WM324/OAK 100205|01 F100 01 CSG 45 EA EA 1 50
165447 100074411 9 12/16/2009 HARD MAPLE 137496|01 F400L 08 S4S 144 EA EA 1 12
165447 100074411 18 12/16/2009 W275/OAK 106892|01 F2830 01 BASE LF LF LF 1 26
165447 100074411 18 12/16/2009 W275/OAK 106892|01 F2830 01 BASE LF LF LF 1 154
165447 100074411 18 12/16/2009 W275/OAK 106892|01 F2830 01 BASE LF LF LF 1 55
165447 100074411 18 12/16/2009 W275/OAK 106892|01 F2830 01 BASE LF LF LF 1 60
165447 100074411 18 12/16/2009 W275/OAK 106892|01 F2830 01 BASE LF LF LF 1 130
165447 100074411 18 12/16/2009 W275/OAK 106892|01 F2830 01 BASE LF LF LF 1 28
165447 100074411 18 12/16/2009 W275/OAK 106892|01 F2830 01 BASE LF LF LF 1 150
165447 100074411 18 12/16/2009 W275/OAK 106892|01 F2830 01 BASE LF LF LF 1 160
165447 100074411 18 12/16/2009 W275/OAK 106892|01 F2830 01 BASE LF LF LF 1 16
165447 100074411 18 12/16/2009 W275/OAK 106892|01 F2830 01 BASE LF LF LF 1 55
165447 100074411 18 12/16/2009 W275/OAK 106892|01 F2830 01 BASE LF LF LF 1 24
165447 100074411 18 12/16/2009 W275/OAK 106892|01 F2830 01 BASE LF LF LF 1 56
165447 100074411 18 12/16/2009 W275/OAK 106892|01 F2830 01 BASE LF LF LF 1 112
165447 100074411 18 12/16/2009 W275/OAK 106892|01 F2830 01 BASE LF LF LF 1 108

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-29 : 14:03:57

Based on your sample data ..this should work...
There are also other (maybe better) ways to do it..by joining the table with itself...but you need to provide the table structure of these 2 tables.

SELECT so_no
, customer_po_no
,line_no
,facility
,facility
,shipped_date
,facility
,customer_part_no
,item_no
,item_description
,facility
,item_uom
,weight_uom
,package_qty_per
,sum(qty) as qty
FROM SHP_CONTAINER_DETAIL,S_O_HEADER
WHERE so_no = s_o_no
AND customer_no = '1214'
AND customer_po_no LIKE '1%'
AND shipped = '1'
AND shipped_date >= '12/16/2009 0:00:00 AM'
AND shipped_date <= '12/16/2009 11:59:59 PM'
group by 
so_no
, customer_po_no
,line_no
,facility
,facility
,shipped_date
,facility
,customer_part_no
,item_no
,item_description
,facility
,item_uom
,weight_uom
,package_qty_per

ferche
Starting Member

2 Posts

Posted - 2009-12-29 : 17:03:10

vijayisonly ,

That did the trick ... thanks for your help !!!!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-29 : 17:10:14

You're welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources