CODEWITHSUNDEEP
mysqlsql
Binouki Jacob
Binouki Jacob

Reputation: 37

SQL Problem regarding to get the sum of duplicate rows

This is my sql query to get the following table below :

select c.name, s.company, p.qty, p.qty * p.price as Total
from client c, purchase p, stock s
where c.clno = p.clno AND s.company = p.company
group by c.name, s.company, p.qty, p.qty * p.price
order by sum(p.qty) desc

The output of the above query looks like this :

Name  |  Company  |  Qty  |  Total
John      ABC         12       100
Bob       XYZ         10       150
John      ABC          5        50
Bob       XYZ         20       250
Bob       XYZ          2        20
Nav       QRS         10       150
John      ABC         10       150

I want to have the query to get the output as the following :

Name  |  Company  |  Qty  |  Total
John      ABC         27       300
Bob       XYZ         32       420
Nav       QRS         10       150

Upvotes: 1

Views: 48

Answers (1)

GMB
GMB

Reputation: 222442

As of now your query uses GROUP BY but does not actually aggregates data. You want to GROUP BY name and company, and SUM the quantities and amounts, like :

select c.name, s.company, SUM(p.qty), SUM(p.qty * p.price) as Total
from client c
inner join purchase p on c.clno = p.clno
inner join stock s on s.company = p.company
group by c.name, s.company
order by Total desc

Other remarks regarding your query :

  • always use explicit joins instead of implicit ones
  • you can use column aliases in the ORDER BY clause (here, Total ; this can make the query easier to read

Upvotes: 1

Related Questions