CODEWITHSUNDEEP
sqlsql-server-2008
Obfuscated
Obfuscated

Reputation: 321

Sub totals on aggregated fields

I understand the use of rollup with "hard" numbers. But I have a couple that I need subtotals on and can't figure out how. My query is:

SELECT entryno
,Section
, Grade, replace(replace('<feet>'' <inches>', '<feet>', Mylength / 12), '<inches>', Mylength % 12) as Mylength
, Pcs
, MyNote
, MyReason
, savedatetime
, secwgt
,(select str(sum(secwgt*pcs*MyLength),12,3) from NYS2RandomReport t2 where (t2.entryno = t.entryno)) as lbs
,(select str(sum(secwgt*pcs*MyLength/2000),12,3) from NYS2RandomReport t2 where (t2.entryno = t.entryno)) as tons
From NYS2RandomReport t
ORDER BY section, grade, mylength

and the return is:

4   W14X120 A588-B  10' 0   7           2015-04-15 12:00:00.000 8.5000      7140.000           3.570
5   W14X120 A588-B  10' 1   6           2015-04-15 12:00:00.000 8.5000      6171.000           3.086
6   W14X120 A588-B  10' 2   3           2015-04-15 12:00:00.000 8.5000      3111.000           1.556
3   W14X120 A992    10' 4   8           2015-04-15 12:00:00.000 8.5000      8432.000           4.216
1   W14X89  A992    20' 0   10  Not for sale    Cracked 2015-04-15 12:00:00.000 8.1666     19599.840           9.800
2   W14X89  A992    21' 5   4   Not for sale    Cracked 2015-04-15 12:00:00.000 8.1666      8395.265           4.198

The last two columns are Lbs and Tons. I need subtotals of those two columns grouped by the section, grade columns.

This has me baffled. Thanks.

Upvotes: 0

Views: 34

Answers (1)

Greg Viers
Greg Viers

Reputation: 3523

Just make sure to do any roll-ups first, then wrap the str() conversion around them after.

(select str(sum(secwgtpcsMyLength),12,3) from NYS2RandomReport t2 where (t2.section = t.section) and (t2.grade=t.grade)) as SectionGradelbs

Upvotes: 1

Related Questions