• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

record selection

is there a way to extract multiple records into 1 record
i am trying the listed statement
select saletype, classDesc, selectyear,
case when yearnumber = 1 then prem_amt end as Year1,
case when yearnumber = 2 then prem_amt end as Year2,    
case when yearnumber = 3 then prem_amt end as Year3,
case when yearnumber = 4 then prem_amt end as Year4
from qClassDescSummaryReport
group by saletype, classDesc, selectyear

it gives me two error messages
Column 'qClassDescSummaryReport.prem_amt' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column 'qClassDescSummaryReport.YearNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

is there a way to use pseudo names  for these missing columns




0
ldcray
Asked:
ldcray
  • 2
2 Solutions
 
BrandonGalderisiCommented:
You're not attempting any aggregation.... if you're trying to do sums, try something like this.
select saletype, classDesc, selectyear, sum(year1), sum(year2), sum(year3), sum(year4)
from 
(select saletype, classDesc, selectyear,
case when yearnumber = 1 then prem_amt else 0 end as Year1,
case when yearnumber = 2 then prem_amt else 0 end as Year2,    
case when yearnumber = 3 then prem_amt else 0 end as Year3,
case when yearnumber = 4 then prem_amt else 0 end as Year4
from qClassDescSummaryReport
) a
group by saletype, classDesc, selectyear

Open in new window

0
 
adatheladCommented:
You should even be able to do it in one select, without the nested query:

select saletype, classDesc, selectyear,
sum(case when yearnumber = 1 then prem_amt else 0 end) as Year1,
sum(case when yearnumber = 2 then prem_amt else 0 end) as Year2,    
sum(case when yearnumber = 3 then prem_amt else 0 end) as Year3,
sum(case when yearnumber = 4 then prem_amt else 0 end) as Year4
from qClassDescSummaryReport
group by saletype, classDesc, selectyear
0
 
BrandonGalderisiCommented:
Absolutely.  That is another completely valid way to do that.  I have found that it's sometimes easier to express in different ways so thanks.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now