CODEWITHSUNDEEP
sqlpostgresql
vinmm
vinmm

Reputation: 297

SQL query help: How to evaluate value/max(value) based on some conditions in same query

Not an expert in SQL. I am using postgres database with EF migration. Stuck with this requirement. Here it goes.

My table is like this:

A   B   C   D

20  1   1   1   

59  0   0   1

57  1   1   1

10  1   0   0

30  1   1   1

15  0   0   0

The order of rows is like oldest to latest(top to bottom).

Half query I have from my project is as below:

SELECT  dcr."A"
FROM "DCR" dcr 
    LEFT JOIN "DCM" dcm ON "Id" = dcm."DCRID"
    LEFT JOIN "DC" dc ON dc."Id" = dcm."DCID" 
WHERE dcr."B" != 0
AND dcr."C" != 0
AND dcr."B" != 0
ORDER BY "UtcDate" desc
limit(1)

This will fetch me the first part value of latest A when it matches condition. But not the Max part and the division part as explained below.

I want to find the result of ((latest A where B = C = D = 1 divided by max of A in its previous rows where B = C = D = 1) - 1) * 100.

I want this to happen in single query and there are multiple groups like this. Lets say the table contains around 60 rows and we can group them based on some other column. Each group should evaluate this above formula.

Expected result for above example should be:

result = ((30 / 57) - 1) * 100 = (0.5263 - 1) * 100 = -47.73

Upvotes: 1

Views: 97

Answers (2)

relhomosapiens
relhomosapiens

Reputation: 11

maybe something like this? select (t1."A"/max(t2."A"))*100 from (select row_number() over() as id,* from t where t."A"=1 and t."B" =1 and t."C"=1 ) as t1 join (select row_number() over() as id,* from t where t."A"=1 and t."B" =1 and t."C"=1 ) as t2 on t1.id>t2.id group by t1."A",t1."E"

Upvotes: 0

Racil Hilan
Racil Hilan

Reputation: 25351

You can use a subquery to get the max. I don't know why you're writing the query in that strange style, but I will keep it:

SELECT  dcr."A" / (SELECT MAX("A")
                   FROM "DCR"
                   WHERE dcr."B" != 0
                     AND dcr."C" != 0
                     AND dcr."D" != 0)) - 1) * 100
FROM "DCR" dcr 
    LEFT JOIN "DCM" dcm ON "Id" = dcm."DCRID"
    LEFT JOIN "DC" dc ON dc."Id" = dcm."DCID" 
WHERE dcr."B" != 0
AND dcr."C" != 0
AND dcr."D" != 0
ORDER BY "UtcDate" desc
limit(1)

Upvotes: 1

Related Questions