Grouping - 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

General SQL Server Forums

New to SQL Server Programming

Grouping

Author

Topic

Deon Smit
Starting Member

47 Posts

Posted - 2008-08-28 : 10:16:36

Hi

I am trying to call record by joining 2 tables. Then want to group them and sum the Qty. It seems because it is unique records it won't group and I can't get a total. Please help.
Script..

select distinct B.batchcartonid, (A.qtyallocated*A.unitprice) as Rand from pickdetail B
join orderdetail A
on A.orderkey=B.orderkey
and A.ORDERLINENUMBER=B.ORDERLINENUMBER
where A.qtyallocated > '0' and
B.batchcartonid > '0'

Script with group that don't work.

select distinct B.batchcartonid, (A.qtyallocated*A.unitprice) as Rand from pickdetail B
join orderdetail A
on A.orderkey=B.orderkey
and A.ORDERLINENUMBER=B.ORDERLINENUMBER
where A.qtyallocated > '0' and
B.batchcartonid > '0'
group by B.batchcartonid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 10:20:33

show some sample data. without seeing data its difficult to understand why its not working.

Deon Smit
Starting Member

47 Posts

Posted - 2008-08-28 : 10:22:07

BATCHCARTONID RAND
------------------------------
0000142670 122.76
0000146415 24.54
0000146415 48.22
0000146415 52.61
0000146415 63.12
0000146415 67.5
0000146415 68.04
0000146415 78.9
0000146415 87.68
0000146416 11.79
0000146416 31.52
0000146418 36.82
0000146418 52.61
0000146418 105.22
0000146418 157.86
0000146418 220.98
0000146419 28.48
0000146419 34.16
0000146419 51.28
0000146419 56.98
0000146419 68.39
0000146419 74.09
0000146419 125.38
0000146419 159.6
0000146420 34.16
0000146420 56.98
0000146420 85.44
0000146420 102.6
0000146420 239.37

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-28 : 10:22:07

Try

select B.batchcartonid, sum(A.qtyallocated*A.unitprice) as Rand from pickdetail B
join orderdetail A
on A.orderkey=B.orderkey
and A.ORDERLINENUMBER=B.ORDERLINENUMBER
where A.qtyallocated > '0' and
B.batchcartonid > '0'
group by B.batchcartonid

Madhivanan

Failing to plan is Planning to fail

Deon Smit
Starting Member

47 Posts

Posted - 2008-08-28 : 10:22:56

BATCHCARTONID RAND
---------------------------
0000142670 122.76
0000146415 24.54
0000146415 48.22
0000146415 52.61
0000146415 63.12
0000146415 67.5
0000146415 68.04
0000146415 78.9
0000146415 87.68
0000146416 11.79
0000146416 31.52
0000146418 36.82
0000146418 52.61
0000146418 105.22
0000146418 157.86
0000146418 220.98
0000146419 28.48
0000146419 34.16
0000146419 51.28
0000146419 56.98
0000146419 68.39
0000146419 74.09
0000146419 125.38
0000146419 159.6
0000146420 34.16
0000146420 56.98
0000146420 85.44
0000146420 102.6
0000146420 239.37

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 10:27:05

where's pickdetail data? just show 5 rows from each table and result you want to get out of 5 rows.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-28 : 10:27:27

Did you try the query I posted?

Madhivanan

Failing to plan is Planning to fail

Deon Smit
Starting Member

47 Posts

Posted - 2008-08-28 : 10:45:07

Thank You !

That Worked.