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 ?

Welcome to SO. I imagine what you're trying to do is possible, but you'll need to give us a lot more info. Sample data, desired results, how you define first, second, third rows. Please put your data as tables in your post, not pictures. — Andrew, Sep 27 '19 at 22:08
How are *1st/2nd/3rd row* defined? They must be either sorted or based on existing data, e.g. *group GROUP2 = secondrow*? — dnoeth, Sep 28 '19 at 11:23
*They are grouped by 'group' and 'type'* But how is 1st/2nd/3rd determined? — dnoeth, Sep 29 '19 at 10:02
*They are grouped by 'group' and 'type'* But how do you determine those groups, why is `GROUP2 shown` the 2nd group? — dnoeth, Sep 30 '19 at 17:15

score 0 · Answer 1 · answered Sep 28 '19 at 08:27

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.

This almost works!! Thanks!! I end up with one row table but I'm getting the error: invalid calculation: division by zero. I don't even have zeros on the table — pachuzzz, Sep 28 '19 at 15:55
I'm guessing one of `a.Q, b.Q, c.Q` values in your query is NULL. Is that the case? If so, how do you want to handle that? You could always put a `COALESCE` around the whole `THEN` expression and have it return a default value if this happens. — ravioli, Sep 28 '19 at 16:14

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

1 Answers1