SQL Grouping query

Question

I have a sql query that runs fine but it returns results from 2 different tables that contain a breakdown of contracts and the projects that belong to them.

An example of this is: Contract number 12004 contains Projects 12004C, 12004D, 12004F

is there a way I can get all 12004 to group together under the 12004 banner contract Number?

My query as it stands to get me the current info is:

SELECT     PA01201.PACONTNUMBER, PA01201.PAPROJNUMBER, PA01201.PAprojname, PA01100.PAcontname
FROM         PA01201 INNER JOIN
             PA01100 ON PA01201.PACONTNUMBER = PA01100.PACONTNUMBER

basically i am trying to get all the figures from PAPROJNUMBERS (C,D,F etc) to form one line of a subtotal under PACONTNUMBER

I have tried a 'Group By' but get a Ambiguous column name 'PACONTNUMBER'???

Any help at all much appreciated.

---

Thanks for the help. Much appreciated. I will keep trying different things.

In response to using aggregates that's not really what I was trying to do.

Basically in my example of projects 12004C, 12004D, 12004F etc I just want them all to wrap up under 12004. so it would look something like this..............

Contract Figures Description:-

12004    25000  SS Bus Station
12005    xxxxx  xxxxxxxx
12006    xxxxx  xxxxxxxx
12007    xxxxx  xxxxxxxx
12008    xxxxx  xxxxxxxx

instead of how it looks at the moment:-

Contract Figures Description

12004     6000  SS Bus Station
12004C 8000     SS Bus Station
12004D 1000 SS Bus Station
12004F 10000    SS Bus Station
12005   xxxxx   xxxxxx

Answer 1

If you get a warning about an ambiguous name, it's because you mention a column that's in more than one table, and the database doesn't know which table you mean.

That being said, depending on the database engine you have (SQLServer, MySQL, Oracle), some have a "concat" function for aggregation.

Answer 2

If you wanna list a contract with all it's projects you'll have to do it the way you are already doing it. the result will look like this:

contract1 project1
contract1 project2

you can't use aggregates to get

contract1
           project1
           project2

that being said, your Ambiguous error is because you need to write

GROUP BY PA01201.PACONTNUMBER

and not

GROUP BY PACONTNUMBER

it still going to complain about all the other columns not being in an aggregate function(e.g. MAX, COUNT, AVG, MIN ....)

Answer 3

SELECT     min(PA01201.PACONTNUMBER) as minPAContNumber, 
           sum(cast(PA01201.PAPROJNUMBER as int)) as SUMPaProjNumber, 
               PA01201.PAprojname
FROM         PA01201 
INNER JOIN PA01100 
  ON PA01201.PACONTNUMBER = PA01100.PACONTNUMBER
GROUP BY PA01201.PAprojname

I'm assuming paprojnumber is a numeric field since your example is doing math on it.

you can't add0 PA01100.PACONTNUMBER back in to the select or group by as it will create the seperate rows you're trying to avoid; unless you use wm_Concat or a similar function to aggregrage all the different projects into the same line/column

UPDATE based on expected results and comments:

12004   25000  SS Bus Station
12005   xxxxx   xxxxxx

is not achievable because paprojnumber contains data such as 0612AB which can not be treated as a number thus when we try and roll up the data into one row adding the projnumbers together, the DBengine can't add 0612AB to the other results.

OR are these results OK?

12004  |8000, 6000, 1000, 10000  | SS BUS STATION
12005  |9000AB, 6000, 2000, 4000 | SS BUS STATION