CODEWITHSUNDEEP
sqlsql-server-2008
Sam Bin Ham
Sam Bin Ham

Reputation: 449

How to use SQL Join with multiple fields and find sum of values

How to SQL Join with multiple fields and sum of values . As per my query i am getting duplicates

job_main (table)
+-----+------+------+------------+
| Loc | Year |  Wo  | total_cost |
+-----+------+------+------------+
|   8 | 2018 | 2402 |       1175 |
+-----+------+------+------------+

Job_line (table)
+-----+------+------+------------+------------+-----------+-----------+-----------+
| loc | Year |  Wo  | labor_cost | parts_cost | total_tax | parts_tax | labor_tax |
+-----+------+------+------------+------------+-----------+-----------+-----------+
|   8 | 2018 | 2402 |          0 |    1202.85 |         0 |         0 |         0 |
|   8 | 2018 | 2402 |          0 |   -1202.85 |         0 |         0 |         0 |
|   8 | 2018 | 2402 |          0 |    1119.05 |     55.95 |     55.95 |         0 |
+-----+------+------+------------+------------+-----------+-----------+-----------+

Result (Expected)

+-----+------+------+------------+------------+------------+-----------+-----------+-----------+
| Loc | Year |  Wo  | total_cost | labor_cost | parts_cost | parts_tax | labor_tax | total_tax |
+-----+------+------+------------+------------+------------+-----------+-----------+-----------+
|   8 | 2018 | 2402 |       1175 |          0 |    1119.05 |     55.95 |         0 |     55.95 |
+-----+------+------+------------+------------+------------+-----------+-----------+-----------+

My Query:

SELECT distinct "JOB_LINE""."LOC", "JOB_LINE""."Year", "JOB_LINE""."Wo"
 , ISNULL(("JOB_MAIN"."total_cost" ), 0) AS "total_cost",  ISNULL(SUM("JOB_LINE""."labor_cost"), 0) AS "labor_cost" 
  ,  ISNULL(SUM("JOB_LINE""."labor_tax"), 0) AS "labor_tax"   ,  ISNULL(SUM("JOB_LINE""."parts_cost"), 0) AS "parts_cost" 
  ,  ISNULL(SUM("JOB_LINE""."total_tax"), 0) AS "total_tax"  ,  ISNULL(SUM("JOB_LINE""."parts_tax"), 0) AS "parts_tax"
  FROM   "proto"."emsdba"."JOB_LINE"" "JOB_LINE"" 
  INNER JOIN  "proto"."emsdba"."JOB_MAIN" "JOB_MAIN"
  ON   "JOB_LINE"".LOC="JOB_MAIN".LOC AND   "JOB_LINE"".year="JOB_MAIN".year AND "JOB_LINE"".Wo="JOB_MAIN".Wo
   GROUP by  "JOB_LINE""."LOC", "JOB_LINE""."Year", "JOB_LINE""."Wo","JOB_MAIN"."total_cost"
  , "JOB_LINE"".labor_cost, "JOB_LINE"".parts_cost, "JOB_LINE"".total_tax,  "JOB_LINE"".parts_tax,"JOB_LINE"".labor_tax

Upvotes: 0

Views: 46

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Although you can use join and group by for this, this problem is also a good candidate for apply:

select m.*,
       coalesce(labor_cost, 0) as labor_cost,
       coalesce(parts_cost, 0) as parts_cost,
       coalesce(parts_tax, 0) as parts_tax,
       coalesce(labor_tax, 0) as labor_tax,
       coalesce(total_tax, 0) as total_tax
from proto.emsdba.job_main m outer apply
     (select sum(jl.labor_cost) as labor_cost,
             sum(jl.parts_cost) as parts_cost, 
             sum(jl.parts_tax) as parts_tax,
             sum(jl.labor_tax) as labor_tax,
             sum(jl.total_tax) as total_tax
      from proto.emsdba.job_line jl
      where m.loc = jl.loc and
            m.year = jl.year and
            m.wo = jl.wo
     ) jl;

Upvotes: 0

James
James

Reputation: 3015

Try with this:

select m.loc,m.year,m.wo,m.total_cost,sum(l.labor_cost),sum(l.parts_cost),sum(l.parts_tax),sum(l.labor_tax),sum(l.total_tax)
from proto.emsdba.job_main m
  join proto.emsdba.job_line l
    on m.loc=l.loc
   and m.year=l.year
   and m.wo=l.wo
group by m.loc,m.year,m.wo,m.total_cost

Basically, you group by the fields in the parent table (shared by all the lines) and sum the values of the lines to get a single record for each key (loc/year/wo)

Upvotes: 1

Related Questions