select distinct columnA and columnB with sum(cloumnC)

Question

I have a table like this:

importer     exporter     quantity    
A            D           0.9
A            B           0.9
A            B           0.1
B            E           9.4
B            E           8.9
B            D           9.4
C            P           9.0
C            V           1.0
C            P           0.9

I want to find the distinct columnA and columnB with the sum(columnC) and the table is ORDER BY SUM(columnC) DESC.

importer     exporter     quantity
B            E           18.3
C            P           9.9
B            D           9.4    
A            B           1.0
C            V           1.0
A            D           0.9

when I tried

 SELECT DISTINCT
 IMPORTER, EXPORTER, QUANTITY
 FROM Tablename;

The table MYsql shows is not distinct columnA and columnB, in fact it shows duplicated columnA and columnB and the columnC is not added up.

Possible duplicate of [How does GROUP BY work?](https://stackoverflow.com/questions/7434657/how-does-group-by-work) — Sam M, Dec 03 '18 at 18:05

score 2 · Answer 1 · answered Dec 03 '18 at 18:05

2

Try this:

SELECT   importer,
         exporter,
         SUM(quantity) AS sum_quantity
FROM     tablename
GROUP BY importer,
         exporter
ORDER BY sum_quantity DESC;

answered Dec 03 '18 at 18:05

Robert Kock

5,622
1
11
20

score 0 · Accepted Answer · answered Dec 03 '18 at 18:05

0

Try like below

 SELECT 
 IMPORTER, EXPORTER, sum(QUANTITY)
 FROM Tablename group by IMPORTER, EXPORTER

answered Dec 03 '18 at 18:05

Zaynul Abadin Tuhin

30,345
5
25
56

it helps me a lot, thx! I also notice that the one you coded has the least duration time. – KnowTooLess Dec 03 '18 at 19:44

GMB · Answer 3 · 2018-12-03T18:24:48.687

0

As hinted by @GordonLinoff, what you need is a GROUP BY query

SELECT 
    IMPORTER, 
    EXPORTER, 
    SUM(QUANTITY)
FROM Tablename
GROUP BY
    IMPORTER, 
    EXPORTER
ORDER BY 
    SUM(QUANTITY) DESC;

edited Dec 03 '18 at 18:24

answered Dec 03 '18 at 18:05

GMB

195,563
23
62
110

score 0 · Answer 4 · answered Dec 03 '18 at 18:06

0

It is the basic GROUP BY:

 SELECT 
 IMPORTER, EXPORTER, SUM(QUANTITY) AS SUMQUANTITY
 FROM Tablename
 GROUP BY IMPORTER, EXPORTER
 ORDER BY SUMQUANTITY DESC;

answered Dec 03 '18 at 18:06

forpas

145,388
9
31
69

select distinct columnA and columnB with sum(cloumnC)

4 Answers4