CODEWITHSUNDEEP
phpmysqlsql
vickeyb4u
vickeyb4u

Reputation: 19

getting sum values based on two where conditions in SQL

I have table as follows:

enter image description here

Highlighted in red color is base PRDS (IM) which will be used for different PCK like:

ABC & XYZ
DEF & XYZ

What I want is as follows:

enter image description here

And

enter image description here

I tried two where conditions but its not working. Kindly help me out to solve this.

following is my code:

$sql="SELECT bom. OUTPRDSRL,
             bom. BOPPRDCD,
             bom. OUTPRDNAME,
             bom. PRDSTGCD,
             SUM(bom. REQQTY) AS RTOTAL,
             SUM(bom. TOTALVALUE) AS RRTOTAL,
             SUM(bom.TOTALVALUE * b_master.calculation) / 100 +
             SUM(bom.TOTALVALUE) AS TOTAL,
             b_master. PRDSTGCD,
             b_master. calculation
        FROM bom
       INNER JOIN b_master
          ON bom. PRDSTGCD = b_master. PRDSTGCD
       WHERE BOPPRDCD = '$BOPPRDCD'
         AND OUTPRDCD = '$OUTPRDCD'
       GROUP BY OUTPRDNAME
       ORDER BY PRDSTGDES";

Upvotes: 0

Views: 57

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

I don't see how your query relates to the tables you are showing. So, here is my answer refering to the tables.

You have a table and want to show all its rows (or all IM rows plus all rows for one name). In these rows you also want to show sums. In order to do that you need SUM OVER.

IM and one name only:

select
  t.*,
  sum(qty) over (partition by prds, name) as subtotal,
  sum(qty) over () as total
from mytable t
where prds = 'IM' or (prds = 'PCK' and name = 'ABC')
order by (prds = 'IM') desc, stg;

All rows:

select
  t.*,
  sum(qty) over (partition by prds, name) as subtotal,
  sum(case when prds = 'IM' then qty end) over () +
  sum(case when prds <> 'IM' then qty end) over (partition by name)
    as total
from mytable t
where prds = 'IM' or (prds = 'PCK' and name = 'ABC')
order by (prds = 'IM') desc, stg;

This will show the sums in every row. Use your GUI layer (your app or Website) to only display the sums where you want them. The same can be done in SQL using LEAD, but it is better to deal with layout things in your GUI layer.

Upvotes: 1

Related Questions