CODEWITHSUNDEEP
sqlcase-when
DWS
DWS

Reputation: 25

case when and multi rows

OK so I've got a question on how to fix getting multi rows for the query below. I was expecting to get all rows with the same "durationage" into one row. I'm sure it a novice mistake but i'm still a newbie at this. Thanks for any help. 

Select UPPERAGE, 
case when [DurationLower] <= 2 THEN [DurationLower] ELSE NULL END AS 'First 
trail year' ,
case when [DurationLower] >= 3 THEN [DurationLower] ELSE NULL END AS 'Next 
trail year',
case when [DurationLower] <= 2 THEN Rate/100 ELSE NULL END AS 'First trail 
%', 
case when [DurationLower] >= 3 THEN Rate/100 ELSE NULL END AS 'Next trail %'

From  Picasso.PageToProduct_VW pp
left join Picasso.PageToProductToRate_vw ppr on ppr.PageToProductID= 
pp.PageToProductID 
left join Picasso.ProductRates_VW r on ppr.ProductRatesID= r.ProductRatesID

wHERE pp.PageID = '7686A1'
and r.RateType = 'BASIS POINTS'
Group by upperage

enter image description here

What i'm looking for is rows 1 & 2 to be combine and rows 3 & 4 to be combine

Upvotes: 0

Views: 69

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You would appear to have multiple UPPERAGE values that look the same. If it is numeric, that could be because they different by a minuscule amount. If it is a string, it could be something like trailing spaces.

Assuming the value is numeric, you can try:

Select floor(UPPERAGE) as upperAge, 
       max(case when [DurationLower] <= 2 THEN [DurationLower] END) AS First_ 
trail_year,
       max(case when [DurationLower] >= 3 THEN [DurationLower] END) AS Next _trail_year,
       max(case when [DurationLower] <= 2 THEN Rate/100 END) AS First_trail_%, 
       max(case when [DurationLower] >= 3 THEN Rate/100 END) AS Next_trail_percent
From  Picasso.PageToProduct_VW pp left join
     Picasso.PageToProductToRate_vw ppr
     on ppr.PageToProductID = pp.PageToProductID left join 
     Picasso.ProductRates_VW r
     on ppr.ProductRatesID= r.ProductRatesID
where pp.PageID = '7686A1' and r.RateType = 'BASIS POINTS'
Group by floor(UPPERAGE)

Notes:

  • As written, your query should fail, because you have unaggregated columns in the select.
  • I'm a strong believer that single quotes should only be used for string and date constants, and not for column aliases.
  • Some databases do integer division -- you should be cognizant of what your database does. You might want rate / 100.0.
  • I have chosen max() as the operator to combine things. sum() could also be appropriate.

Upvotes: 0

Lamak
Lamak

Reputation: 70638

I'm not sure how this current query is working (unless you are using mysql, but the screenshot seems to be SQL Server).

Anyway, you need to use an aggregation function on the CASE expressions (I'm using SUM):

SELECT  UPPERAGE, 
        SUM(CASE 
                WHEN [DurationLower] <= 2 THEN [DurationLower] 
                ELSE NULL 
            END) AS [First trail year],
        SUM(CASE 
                WHEN [DurationLower] >= 3 THEN [DurationLower] 
                ELSE NULL 
            END) AS [Next trail year],
        SUM(CASE 
                WHEN [DurationLower] <= 2 THEN Rate/100 
                ELSE NULL 
            END) AS [First trail %], 
        SUM(CASE 
                WHEN [DurationLower] >= 3 THEN Rate/100 
                ELSE NULL 
            END) AS [Next trail %]
FROM  Picasso.PageToProduct_VW pp
LEFT JOIN Picasso.PageToProductToRate_vw ppr 
    ON ppr.PageToProductID = pp.PageToProductID 
LEFT JOIN Picasso.ProductRates_VW r 
    ON ppr.ProductRatesID = r.ProductRatesID
WHERE pp.PageID = '7686A1'
AND r.RateType = 'BASIS POINTS'
GROUP BY upperage
;

Upvotes: 1

Related Questions