CODEWITHSUNDEEP
sqljoingroup-by
DDXEbeling
DDXEbeling

Reputation: 17

SQL group column where other column is equal

I'm trying to select some information from a database. I get a database with columns like:

Ident,Name,Length,Width,Quantity,Planned

Table data is as follow

+-----------+-----------+---------+---------+------------+---------+
|   Ident   |   Name    | Length  | Width   | Quantity   | Planned |
+-----------+-----------+---------+---------+------------+---------+
|   12345   | Name1     | 1500    |    1000 |         20 |       5 |
|  23456    | Name1     | 1500    |    1000 |         30 |      13 |
|  34567    | Name1     | 2500    |    1000 |         10 |       2 |
|  45678    | Name1     | 2500    |    1000 |         10 |       4 |
|  56789    | Name1     | 1500    |    1200 |         20 |       3 |
+-----------+-----------+---------+---------+------------+---------+

my desired result, would be to group rows where "Name,Length and Width" are equal, sum the "Quantity" and reduce it by the sum of "Planned" e.g:

- Name1,1500,1000,32 --- (32 because (20+30)-(5+13))
- Name1,2500,1000,14 --- (14 because (10+10)-(2+4)))
- Name1,1500,1200,17

now I got problems how to group or join these information to get the wished select. may be some you of can help me.. if further information's required, please write it in comment.

Upvotes: 0

Views: 1410

Answers (2)

JSthebest
JSthebest

Reputation: 21

select 

A1.Name,A1.Length,A1.Width,((A1.Quantity + A2.Quantity) -(A1.Planned+A2.Planned))

from `Table` AS A1, `Table` AS A2

where A1.Name = A2.Name and A1.Length = A2.Length and A1.Width = A2.Width

group by (whatever)

So you are comparing these columns form the same table?

Upvotes: 0

M. Kanarkowski
M. Kanarkowski

Reputation: 2195

You can achieve it by grouping your table and subtract sums of Quantity and Planned.

select
     Name
    ,Length
    ,Width
    ,sum(Quantity) - sum(Planned)
from yourTable
group by Name,Length,Width

Upvotes: 4

Related Questions