Calculate product of column values on the basis of other column in SQL Server

Question

I have a table

Tid   Did    value
------------------
1     123    100
1     234    200
2     123    323
2     234    233

All tids have dids as 123 and 234. So for every tid having dids 123 and 234 I want the product of corresponding values

The output table will be

Tid   Product
------------------
1     20000 (product of 100 and 200)
2     75259 (product of 323 and 233)

Any help?

This question does not show any research effort. – Tab Alleman Jul 14 '17 at 14:26 — Tab Alleman, Jul 14 '17 at 14:26

score 0 · Answer 1 · answered Jul 14 '17 at 13:44

select tid,
         min(case when did = 123 then value end)
       * min(case when did = 234 then value end) product
  from my_table
 group by tid

To get the data for multiple rows combined (based on tid) you use GROUP BY.

Because you're grouping by tid, you have to use an aggregate function to do anything with other values from the individual rows. If implied assumptions hold (exactly 1 row matching each did for each tid) then it doesn't matter much what aggregate function you use; min is as good as anything.

Within the aggregation, you use CASE logic to select value for the required did (and NULL for all other rows in the tid group).

Then just do the math.

Having same relatable issue posted the question but not quite get the answer.Link is https://stackoverflow.com/questions/45086538/combine-columns-values-using-comma-using-the-other-column-in-sql-server/45088980#45088980.Any help with this — Ritesh, Jul 14 '17 at 14:05

score 0 · Answer 2 · answered Jul 14 '17 at 13:48

0

You can use some arithmetic to get the product per tid.

select tid,exp(sum(log(value))) as prod
from t
group by tid

To do this only for tid's having did values 123 and 234, use

select tid,exp(sum(log(value))) as prod
from t
group by tid
having count(distinct case when did in (123,234) then did end) = 2

answered Jul 14 '17 at 13:48

Vamsi Prabhala

48,685
4
36
58

Having same relatable issue posted the question but not quite get the answer.Link is https://stackoverflow.com/questions/45086538/combine-columns-values-using-comma-using-the-other-column-in-sql-server/45088980#45088980 any help with this – Ritesh Jul 14 '17 at 14:06
@Ritesh maybe if these answers help with/solve your problem you can mark the correct one as accepted, then people will be more inclined to help you with other questions. or if they don't help, comment why and people can try to correct it. – ADyson Jul 14 '17 at 14:41

score 0 · Answer 3 · answered Jul 14 '17 at 14:51

Here's a Rexster solution, based on good work of @gbn here

SELECT
    Tid,
    CASE
       WHEN MinVal = 0 THEN 0
       WHEN Neg % 2 = 1 THEN -1 * EXP(ABSMult)
       ELSE EXP(ABSMult)
    END
FROM
    (
    SELECT
       Tid,
       SUM(LOG(ABS(NULLIF(value, 0)))) AS ABSMult,
       SUM(SIGN(CASE WHEN value < 0 THEN 1 ELSE 0 END)) AS Neg,
       MIN(ABS(value)) AS MinVal
    FROM
       t
    GROUP BY
       Tid
    ) t2

Calculate product of column values on the basis of other column in SQL Server

3 Answers3