Need help adding Group By Or Count

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

General SQL Server Forums

New to SQL Server Programming

Need help adding Group By Or Count

Author

Topic

Jlamb62480
Starting Member

10 Posts

Posted - 2014-11-03 : 18:29:48

Hi everyone. I was assisted a while back writing this following code which includes some CTE. I'll list the code and the result below, but then what I am trying to accomplish is to get the total number of distinct values in the 'lpn' column rather than the 'lpn' value itself based on grouping by the Order column. Any help/insight would, as always, be much appreciated.

[WITH Cte AS(select pw.schbat AS Wave,
adrmst.adrnam AS 'Ship to Address',
adrmst.adrln1 AS 'address continued',
adrmst.adrcty AS city,
adrmst.adrstc AS State,
adrmst.adrpsz AS 'zip code',
invlod.lodnum AS LPN,
shipment.host_ext_id AS 'Host Number',
shipment_line.ordnum AS 'Order',
car_move.car_move_id AS 'Carrier Move ID',
ROW_NUMBER() OVER(PARTITION BY invlod.lodnum
ORDER BY shipment_line.ordnum) AS rn
from aremst
join locmst
on (aremst.arecod = locmst.arecod)
and (aremst.wh_id = locmst.wh_id)
join invlod
on (locmst.stoloc = invlod.stoloc)
and (locmst.wh_id = invlod.wh_id)
join invsub
on (invlod.lodnum = invsub.lodnum)
join invdtl
on (invsub.subnum = invdtl.subnum)
join shipment_line
on (shipment_line.ship_line_id = invdtl.ship_line_id)
join shipment
on (shipment_line.ship_id = shipment.ship_id)
join ord
on (ord.ordnum = shipment_line.ordnum)
join adrmst
on (ord.st_adr_id = adrmst.host_ext_id)
left outer
join stop
on (stop.stop_id = shipment.stop_id)
left outer
join car_move
on (stop.car_move_id = car_move.car_move_id) left
join pckwrk pw
on invdtl.wrkref = pw.wrkref
where car_move.car_move_id = 'sdqd_00900')
SELECT *
FROM Cte
WHERE rn = 1];

quote:

wave ship to address address continued city state zip code lpn host number order carrier move id rn
1234 abcdef abc ac gg 12345 00200212000010098506 43296 XE89579 ggrrddff 1
1234 abcdef abc ac gg 12345 00200212000010098513 43295 XE89577 ggrrddff 1
1234 abcdef abc ac gg 12345 00200212000010590840 43292 XE89568 ggrrddff 1
1234 abcdef abc ac gg 12345 00200212000082363014 43298 XE89589 ggrrddff 1
1234 abcdef abc ac gg 12345 00200212000082363021 43293 XE89563 ggrrddff 1
1234 abcdef abc ac gg 12345 00200212000082363038 43293 XE89563 ggrrddff 1
1234 abcdef abc ac gg 12345 00200212000082363045 43293 XE89563 ggrrddff 1
1234 abcdef abc ac gg 12345 00200212000082363052 43293 XE89563 ggrrddff 1
1234 abcdef abc ac gg 12345 00200212000082363069 43293 XE89563 ggrrddff 1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-03 : 18:41:49

SELECT Order, LPN, COUNT(*)
FROM Cte
GROUP BY Order, LPN

If that doesn't help, show us the result set you are expecting based on the sample data you provided.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Jlamb62480
Starting Member

10 Posts

Posted - 2014-11-03 : 19:01:20

The result set I am hoping for (sorry I neglected to mention prior) is as shown below. This result would count the number of unique 'lpn'. Whereas before there had been a list of 43 unique 'lpn' numbers. Each associated with a particular 'order' number.

quote:

wave ship to address address continued city state zip code lpn host number order carrier move id rn
1234 abcdef abc ac gg 12345 6 43293 XE89563 ggrrddff 1
1234 abcdef abc ac gg 12345 5 43292 XE89568 ggrrddff 1
1234 abcdef abc ac gg 12345 6 43294 XE89572 ggrrddff 1
1234 abcdef abc ac gg 12345 6 43295 XE89577 ggrrddff 1
1234 abcdef abc ac gg 12345 9 43296 XE89579 ggrrddff 1
1234 abcdef abc ac gg 12345 5 43297 XE89586 ggrrddff 1
1234 abcdef abc ac gg 12345 6 43298 XE89589 ggrrddff 1

Jlamb62480
Starting Member

10 Posts

Posted - 2014-11-04 : 20:12:31

Thank you tkizer, I was able to make it work. There were just some syntax issues that needed sorted out. Many thanks for your time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources