Thread: CASE WHEN vs. BETWEEN

Try something like this

Code:

  Select S.s_id, S.s_name, S.s_symbol, S.s_publisher, S.s_published_date
       , sum(Case when SD.sd_shares > 0 then SD.sd_shares else 0 end) as sd_shares
       , case
           when SD.sd_shares > 0 then (SELECT (SUM(sd_shares * sd_price_per_share) / SUM(sd_shares)) FROM tblStocks_detail) 
           ELSE 0 
         END As sd_price_per_share
       , MD.sd_date
    From dblStocks S
    Left Join tblStocks_Detail SD
      On S.s_id = sd.s_id
    Join (
          Select s_id, max(sd_date) as sd_date
            From tblStocks_Detail
         Group by s_id
         ) as MD
      On (S.s_id = MD.s_id)
   Group By  S.s_id, S.s_name, S.s_symbol, S.s_publisher, S.s_published_date, MD.sd_date

Also, I think that

Code:

       , case
           when SD.sd_shares > 0 then (SELECT (SUM(sd_shares * sd_price_per_share) / SUM(sd_shares)) FROM tblStocks_detail) 
           ELSE 0 
         END As sd_price_per_share

will give you the AVG price per share over the whole database, not just the stock in question (i.e. you'll get the same number in each row).

It is still giving me this error:

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

When you add the sd_share to the GROUP BY, it shows each individual row, as I stated before.

Also, the average price per share, as you were saying, is the same across the board. However, in my original SQL, it was calculating properly.

OK, while I noodle over this a second, try taking out

Code:

       , case
           when SD.sd_shares > 0 then (SELECT (SUM(sd_shares * sd_price_per_share) / SUM(sd_shares)) FROM tblStocks_detail) 
           ELSE 0 
         END As sd_price_per_share

from the query and see if works then. If it does, I'm on the right track.

Well, partially. The last trade date works. The total shares works. What does not work is the query showing all records in the tblStocks table. For those stocks that have no records in the tblStocks_detail table, it is not showing the stock. It should show the stock name with 0 shares and the last trade date of 'N/A'.

Well, progress is progress. While I cogitate on that last bit, does the follow line, which replaces the case above, give you the Avg PPS you are looking for?

Code:

avg(Case when SD.sd_shares > 0 then sd_price_per_share end) as sd_avg_price_per_share

No, because it is really not a straight average. It is a weighted average of all shares purchased and the price purchased. So, if you purchased 50 shares @ $20.00 and 100 shares at $10.00, your average is not $15.00, but rather $13.33 ($2,000 / 150 shares).

OK, my bad. My second join should have been a LEFT JOIN rather then the inner join.

Originally Posted by BLaaaaaaaaaarche

No, because it is really not a straight average. It is a weighted average of all shares purchased and the price purchased. So, if you purchased 50 shares @ $20.00 and 100 shares at $10.00, your average is not $15.00, but rather $13.33 ($2,000 / 150 shares).

Ah, OK. back to the drawing board. Probably going to be another JOIN to a sub-query then.

Here's my best guess without coding up some sample tables to try it on:

Code:

  Select S.s_id, S.s_name, S.s_symbol, S.s_publisher, S.s_published_date
       , sum(Case when SD.sd_shares > 0 then SD.sd_shares else 0 end) as sd_shares
       , AP.sd_price_per_share
       , MD.sd_date
    From tblStocks S
    Left Join tblStocks_Detail SD
      On S.s_id = sd.s_id
    Left Join (
          Select s_id, max(sd_date) as sd_date
            From tblStocks_Detail
            Group by s_id
          ) as MD
      On (S.s_id = MD.s_id)
    Left Join (Select sd_id
                    , SUM(sd_shares * sd_price_per_share) / SUM(sd_shares) as sd_price_per_share
                 From tblStocks_Detail
                 Where sd_shares > 0
                 Group By sd_id
                )  as AP
   Group By  S.s_id, S.s_name, S.s_symbol, S.s_publisher, S.s_published_date, AP.sp_price_per_share, MD.sd_date

Hopefully that's close.

That seems to work (with a few minor tweaks). Now, how do I incoporate my CASE statements for the price_per_share and date fields. If the number of shares = 0, then I want to show 0 for the price_per_share. Also, same goes for the last trade date. If no trades, then show 'N/A'. Where do I put the CASE for these two?