Problem in sql code...

Question

Problem in sql code...

Christopher Klein

SSCrazy

Points: 2648
More actions
October 24, 2005 at 1:40 pm

#169554

Greetings,
Hate coming over here for something like a programming issue but...
I have a script that I cannot figure out how I can group things to get the results that I want out of it
SELECT
P.intMasterAcctID,
P.vchrMap,
P.vchrBlock,
P.vchrLot,
A.intTaxCategory,
(Select vchrCode from LandUseCodes where intID=a.intLandUseCode) [Land Use Code],
convert(decimal(24,5),A.lngLandAreaAcre) as [Total Land Area],
A.lngLandValue as [Total Land Value],
A.lngAgricultureCredit as [Agriculture Credit],
A.lngYardItemsValue as [Yard Items Value],
A.lngBuildingValue as [Building Value],
A.lngTotalAssessed as [Total Assessed],
(Select vchrCode from LandUseCodes where intID=a.intMixedLandUseCode) [Mixed Usage]
FROM
Propertys P left outer join AssessmentSummarys A
on P.intMasterAcctID=A.intMasterAcctID
where
P.intActive = 1
and p.intMasterAcctID=19
The data comes out like this: (tab-delimited)
intMasterAcctID vchrMap vchrBlock vchrLot intTaxCategory [Land Use Code] [Total Land Area] [Total Land Value] [Agriculture Credit] [Yard Items Value] [Building Value] [Total Assessed] [Mixed Usage]
19 201 16 0 7 712 6.46700 6170.0000 10030.0000 .0000 .0000 6170.0000 NULL
19 201 16 0 7 718 5.20000 598.0000 12402.0000 .0000 .0000 598.0000 NULL
19 201 16 0 7 717 21.22000 2016.0000 51084.0000 .0000 .0000 2016.0000 NULL
19 201 16 0 3 317 .00000 .0000 .0000 .0000 .0000 .0000 NULL
19 201 16 0 7 712 1.03306 986.0000 6214.0000 .0000 .0000 986.0000 NULL
19 201 16 0 3 317 .00000 .0000 .0000 .0000 .0000 .0000 NULL
The problem is that I want to GROUP by intTaxCategory and [Land Use Code] for all of the next 7 columns.
Anytime I try to group with anything I get Column 'P.intMasterAcctID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. for all of the fields NOT in the group.
Is there a way I can get what I want out without having to use a cursor and throwing values in buckets of my own making before I spit the data out how I want it?
thanks,
Chris

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Select the data you need with the group by (without the next 7 columns). Make that a derived table and rejoin back to the base table and then include the 7 said columns.

Christopher Klein SSCrazy Points: 2648 More actions · Answer 2

Hi there,

Thanks for the reply but in looking at the code I remember that I already had this argument with my boss. He basically wants one line by intMasterAcctID but since he needs to have intTaxCategory and [Land Use Code] which are different, its breaking down to the combined lowest common denominator.

By summing the values I can reduce it to 4 results (combining 7/712, 3/317) but theres no way that he could get it to 1 line with that data.

Sorry to take up the time and space but I'm the only SQL person here so I have nobody to 'talk aloud' to here besides myself.

regards,

Chris

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Maybe you can give him what he wants (or else give him what he needs). Maybe you can give him 2 different reports (assuming those are just flag bits). Other than that try to give the the omellette without broken eggs analogy .

Greg Young SSChasing Mays Points: 626 More actions · Answer 4

I can't tell what's OK to aggregate. It looks like there's 4 discrete combos of tax cat & land use codes for the sample. Can't really flatten it beyond that without making some decisions. Why don't you take the sample results you posted and manually reformat them to whatever your boss wants to see. Then post the result back here and we can help you with a query.

Regards,

Greg Young