CODEWITHSUNDEEP
sqloracle-databasejoinsum
Ludo
Ludo

Reputation: 3

SQL - SUM different rows

I want to sum prices from a table linked to another.

I try this query :

SELECT COUNT(cmd.mt14),
SUM(cmd.mt14)
FROM g_piece cmd
INNER JOIN g_piece lot
ON lot.GPIREFNOTAIRE = cmd.refpiece
AND lot.gpirole    = 'B2029005'

And results are :

count(cmd.mt14)    sum(cmd.mt14)
       100           170704,188

It's not good because it sums all deuplicated lines because of the left join. If I try with "unique" :

SELECT count(unique cmd.mt14), 
sum(unique cmd.mt14)
FROM g_piece cmd
INNER JOIN g_piece lot
ON lot.GPIREFNOTAIRE = cmd.refpiece
AND lot.gpirole = 'B2029005'

Results:

count(unique cmd.mt14)    sum(unique cmd.mt14)
       54                       57724,554

The problem is that's wrong too, because the sum function is not going to sum ALL prices, just unique prices.

How can I sum all prices based on different column?

Thanks a lot

If I try to get rows and SUM all lines on excel, I have:

count     sum
58        58492.503

Upvotes: 0

Views: 2524

Answers (3)

xQbert
xQbert

Reputation: 35343

I see two problems:

  1. Your where clause makes your left join an inner, so move the limit to the join, or change the left join to an inner.
  2. When aggregrating data, you must get sums before joins occur if you want to not have duplicates included thus a sub query.

. 

SELECT count_mt14, sum_mt14
FROM (SELECT count(cmd.mt14) count_mt14, sum(mt14) sum_mt14, Liebelle_20_1 
      FROM g_Piece group by liebelle_20_1) cmd
LEFT JOIN g_individu ind
   ON ind.refindividu = cmd.LIBELLE_20_1
LEFT JOIN g_piece lot
   ON lot.GPIREFNOTAIRE = cmd.refpiece
  AND lot.gpirole = 'B2029005'

Upvotes: 0

Ludo
Ludo

Reputation: 3

Thanks a lot for all your help, finally I did with this query :

SELECT SUM(comm.mt14),
  COUNT(comm.refpiece)
FROM
  (
  SELECT UNIQUE cmd.refpiece,
    cmd.mt14
  FROM g_piece cmd
  INNER JOIN g_piece lot
  ON lot.GPIREFNOTAIRE = cmd.refpiece
  WHERE lot.gpirole    = 'B2029005'
  ) comm

Upvotes: 0

Bill Gregg
Bill Gregg

Reputation: 7147

Your second "LEFT JOIN" must always be satisfied since you are using one of its columns in your WHERE clause. And you are not using the IND table at all. So why not just:

SELECT count(cmd.mt14), 
sum(cmd.mt14)
FROM g_piece cmd
INNER JOIN g_piece lot
ON lot.GPIREFNOTAIRE = cmd.refpiece
WHERE lot.gpirole = 'B2029005'

If that isn't what you are looking for, then perhaps you are just trying to verify that your CMD exists for a specific lot:

SELECT count(cmd.mt14), 
sum(cmd.mt14)
FROM g_piece cmd
WHERE EXISTS (select 1 from g_piece lot
              where lot.GPIREFNOTAIRE = cmd.refpiece
              AND   lot.gpirole = 'B2029005')

Upvotes: 1

Related Questions