Unusual SQL calculated column for ActiveDataProvider

Yii 2.0 General Discussions
1

Hi folks,

Hoping to get some help from others as I am a bit clueless here.

I have a table of product, like this: -

###########################

ID # TYPE # PRICE

###########################

1 # 1 # 15

2 # 1 # 10

3 # 1 # 11

4 # 1 # 25

5 # 1 # 15

6 # 2 # 80

7 # 2 # 15

8 # 2 # 16

9 # 2 # 15

10 # 2 # 68

11 # 3 # 15

12 # 3 # 17

13 # 3 # 15

14 # 3 # 13

###########################

What I need to do is to produce a calculated column that gives the accumulative price for a certain type minus the price of the individual product. For instance, using product with ID1, I would need: -

((15 + 10 + 11 + 25 + 15) - 15)

for product ID2, I would need: -

((15 + 10 + 11 + 25 + 15) - 10)

And so on.

Is possible to produce this field? I consider myself fairly decent with Yii2 and SQL but I dont know where to start with this one!

As always, any help greatly appreciated…

U4EA

2

Can you write this in plain SQL in a single query?

How about this… (just thinking out loud, haven’t tested it)




SELECT

  a.id,

  (SELECT p.type FROM product p WHERE p.id=a.id GROUP BY type) as type

FROM product a

I suppose you can also get the result you want with a self-join.

Once you get this working it should be fairly trivial to get it into an ActiveRecord format.