I want to do a calculation inside a select from specific data of the same column. Is this possible?

Question

I want to do a SUM calculation who calls multiple times my 'Q' and QBUYERS column, but within different rows this is my table:

group   type   Q     QBUYERS
GROUP1  sent   300   200 
GROUP2  shown  400   100
GROUP3  sent   200   150`

And this is what I'm trying to do:

SELECT 
  notification_batch_id AS group,
  (secondrow) QBUYERS AS PAYERS_EXPOSED,
  SUM(
    CASE 
      WHEN (firstrow)Q <> 0 AND (secondrow)Q <> 0 AND (thirdrow)Q <> 0 
        THEN (
          (
            ((firstrow)QBUYERS / (firstrow)Q) - 
            ((thirdrow)QBUYERS / (thirdrow)Q) 
          ) / ((secondrow)Q / (firstrow)Q) 
        ) 
      ELSE 0 
    END
  ) AS CVR_INCREMENTAL FROM MYTABLE
GROUP BY group;

Basically doing calculations between my numeric values and then grouping my results. But I don't know how to 'invoke' my 300, 400, 200 etc without selecting the entire column.

I also tried this:

SELECT 
  b.QBUYERS AS PAYERS_EXPOSED,
  SUM(
    CASE 
      WHEN a.Q <> 0 AND b.Q <> 0 AND c.Q <> 0 
        THEN (((a.QBUYERS / a.Q) - (c.QBUYERS / c.Q)) / (b.Q/a.Q)) 
      ELSE 0 
    END
  ) AS CVR_INCREMENTAL
FROM (
  SELECT * FROM BASE_FIRST a
  UNION 
  SELECT * FROM BASE_SECOND b
  UNION 
  SELECT * FROM BASE_THIRD c
) AS mytable

BASE_FIRST being:

group   type  Q    QBUYERS
GROUP1  sent  300  200`

BASE_SECOND being:

group   type  Q    QBUYERS
GROUP2  sent  400  100`

and so on... but it didnt work.

Is this possible to do on TERADATA ?

Answer 1

I'm not 100% sure I follow your logic here, but if you want to run a query that matches only this specific case you describe, then your second query is pretty close.

Just modify it slightly to use JOINs instead of UNIONs. That way you'll end up with single row where you can compare the values in your SUM() instead of multiple rows:

SELECT 
  b.QBUYERS AS PAYERS_EXPOSED,
  SUM(
    CASE 
      WHEN a.Q <> 0 AND b.Q <> 0 AND c.Q <> 0 
        THEN (
          (
            (a.QBUYERS / a.Q) - 
            (c.QBUYERS / c.Q) 
          ) / (b.Q / a.Q) 
        ) 
      ELSE 0 
    END
  ) AS CVR_INCREMENTAL 
FROM MYTABLE a
INNER JOIN MYTABLE b ON b.group = 'GROUP2'
INNER JOIN MYTABLE c ON c.group = 'GROUP3'
WHERE a.group = 'GROUP1'

This assumes that there are three only rows in your table -- one each for GROUP1, GROUP2, GROUP3.

If you want to handle a different, more complex scenario, please update the question with this info.