3

I am working on a SQL Query. Query is something like below:

Select 
    a.field1,
    b.field2,
    c.field3,
    c.field4,
    b.filed5,
    a.field6,
    d.field7 
from 
    a 
    Inner join b on a.field1 = b.field1 
    right join c on b.field2 = c.field3 
    left  join d on d.filed3 = a.field1 
where some conditions;

The output of the above would be something like this:

field1 | field2 | field3 | field4 | field5 | field6 | field7
--------------------------------------------------------------
name   | value1 | other1 | 1      | diff   |   new  | 100
name1  | value2 | other2 | 1      | diff1  |   new1 | 100
name2  | value3 | other3 | 2      | diff2  |   new2 | 100

So i need a new column in the result which sums the field7 based on field4 value(if they are same).

Is it in possible in SQL? I tried to use Group by field 4 here but I am getting an error that field1 should be used in group by. So i am not able get the result as expected.

Expected Result:

field1 | field2 | field3 | field4 | field5 | field6 | field7
--------------------------------------------------------------
name   | value1 | other1 | 1      | diff   |   new  | 200
some   | value3 | other3 | 2      | diff2  |   new2 | 100

Basically i want to remove one column based on a condition and sum the last field.

Any suggestions are helpful.

GMB
  • 216,147
  • 25
  • 84
  • 135
ging
  • 117
  • 8
  • 1
    Would you please show us the results that should correspond to your sample data? – GMB Mar 28 '19 at 00:09
  • @GMB Done. So basically i want to remove the column based on field 1 after summing up the last field as mentioned above – ging Mar 28 '19 at 00:15
  • 1
    Thanks! Another question: do `field1`...`field6` all have equal values in the resultset? If not, which record would you want to keep in the group? – GMB Mar 28 '19 at 00:19
  • You probably want to replace `'some'` in your sample data with some more meaningful data. – GMB Mar 28 '19 at 00:20
  • 1
    Thanks for your effort. In my opinion, your question looks good now! – GMB Mar 28 '19 at 00:35
  • This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. [ask] PS Your descriptions of what you want are unclear. Eg You don't explain "based on". Eg A "basically" or "essentially" or "in other words" that doesn't introduce or summarize a clear, precise & full description that you also give just means "unclearly". – philipxy Mar 28 '19 at 03:02
  • Please in code questions give a [mcve]--cut & paste & runnable code plus desired output plus clear specification & explanation. Minimal means adding minimal problem code to minimal working code. So give minimal code that you show does what you expect & minimal code with the first place you go wrong. (Debugging fundamental.) But in this post: It is not necessary to involve any joins of multiple tables to ask this question. We can't cut & paste. You don't give the error messsage. And that should be a *first* question before this one. – philipxy Mar 28 '19 at 03:05
  • Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) – philipxy Mar 28 '19 at 03:06

2 Answers2

2

To compute the sum of field7 in groups of records having the same field4, you can use window function SUM(...) OVER(...).

To just keep one record per group of records having the same field4, you can use ROW_NUMBER() in an inner query and filter out unwanted records in the outer query. Please note that you need a sort criteria to reliably choose which record should be kept in each group. I chose to use field1 (change it as you need):

Your (pseudo-)query should look like:

SELECT * FROM (
    SELECT 
        a.field1,
        b.field2,
        c.field3,
        c.field4,
        b.filed5,
        a.field6,
        SUM(d.field7) OVER(PARTITION BY c.field4) sm,
        ROW_NUMBER()  OVER(PARTITION BY c.field4 ORDER BY a.field1) rn,
    FROM
        a 
        INNER JOIN b on a.field1 = b.field1 
        RIGHT JOIN c on b.field2 = c.field3 
        LEFT  JOIN d on d.field3 = a.field1 
    WHERE some conditions
) x
WHERE rn = 1

Hint: you can run the inner query independently to see what it returns (this helps understanding the logic).

GMB
  • 216,147
  • 25
  • 84
  • 135
  • looks like the Over Partition is giving me the wrong sum. Instead of 200, 100 i am getting 300,200 for the field7. – ging Mar 28 '19 at 00:43
  • row number() didnt worked but Over Partition worked. Thanks . I have started a different thread to have more understanding on the other point. – ging Mar 28 '19 at 01:53
0

You can use partition by the query to create a separate partition according to field4 and then take the sum

SELECT
z.* FROM (SELECT
a.field1,
b.field2,
c.field3,
b.filed5,
a.field6,
SUM(d.field4) OVER (PARTITION BY a.field1, b.field2, c.field3, b.filed5, a.field6) AS field7,
ROW_NUMBER() OVER (PARTITION BY c.field4 ORDER BY c.field4) AS rank FROM a INNER JOIN b
ON a.field1 = b.field1 RIGHT JOIN c
ON b.field2 = c.field3 LEFT JOIN d
ON d.filed3 = a.field1 where some conditions; ) z WHERE z.rank = 1
AmilaMGunawardana
  • 1,604
  • 2
  • 13
  • 32