mysql how to display values on one row for each po

Question

I need to display one row for each portfolio/stock combination, showing the payment for each month, so if a company pays twice a year in May and September of 441 and 215 respectively this would be shown in one row. Currently my query is outputting a row for each payment, with columns Jan - Dec

I have tried using a Subquery to no avail, I am running out of ideas on how to achieve this.

SELECT
    s.symbol AS Symbol,
    p.code AS Portfolio,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Jan' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Jan,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Feb' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Feb,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Mar' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Mar,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Apr' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Apr,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'May' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS May,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Jun' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Jun,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Jul' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Jul,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Aug' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Aug,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Sep' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Sep,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Oct' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Oct,
    (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Nov' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Nov

    FROM
    dm_transactions t
    INNER JOIN dm_dividends d ON (d.stock_id = t.stock_id)
    INNER JOIN dm_stocks s ON (s.id = t.stock_id) 
    INNER JOIN dm_portfolios p ON (t.portfolio_id = p.id)
    WHERE
    ANY_VALUE(d.pdate) >= CURDATE()
    AND
    p.code = 'SFT_DEA_CO'
    AND
    s.symbol = 'AV..L'
    AND
    t.user_id =2
    GROUP BY
    s.symbol, d.pdate

Output

Symbol Portfolio  Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct 
AV..L  SFT_DEA_CO NULL NULL NULL NULL 441  NULL NULL NULL NULL NULL 
AV..L  SFT_DEA_CO NULL NULL NULL NULL NULL NULL NULL NULL 215  NULL 

Required Output below

Symbol Portfolio  Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct 
AV..L  SFT_DEA_CO                     441                 215 

I require one row for a given Symbol, Portfolio and without the NULL values.

Any help much appreciated.

Thanks

Colin

Answer 1

You could use an aggregation function on a subquery

select  t.Symbol, t.Portfolio
  , max(Jan)
  , max(feb)
  , max(Mar)
  , max(Apr)
  , max(May)
  , max(Jun)
  , max(Jul)
  , max(Aug)
  , max(Sep)
  , max(Oct)
  , max(Nov)
from  (
  SELECT
  s.symbol AS Symbol,
  p.code AS Portfolio,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Jan' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Jan,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Feb' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Feb,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Mar' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Mar,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Apr' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Apr,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'May' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS May,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Jun' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Jun,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Jul' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Jul,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Aug' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Aug,
  nax(case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Sep' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Sep,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Oct' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Oct,
  (case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Nov' then IFNULL(CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100),0) end) AS Nov

  FROM dm_transactions t
  INNER JOIN dm_dividends d ON (d.stock_id = t.stock_id)
  INNER JOIN dm_stocks s ON (s.id = t.stock_id) 
  INNER JOIN dm_portfolios p ON (t.portfolio_id = p.id)
  WHERE ANY_VALUE(d.pdate) >= CURDATE()
  AND p.code = 'SFT_DEA_CO'
  AND s.symbol = 'AV..L'
  AND t.user_id =2
  GROUP BY    s.symbol,d.pdate
) t
GROUP BY   symbol, code