Databases
StackOverflow2013
Postsdbo
POGROUP BY confusion
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGROUP BY confusion
    primarykey
    data
    text
    <p>I am pulling through some data in SQL Server and I would like to <code>GROUP BY</code> my results but cannot get it to work.</p> <p>This is my current SQL statement:</p> <pre><code>SELECT PA01201.CUSTNMBR, PA01201.PACONTNUMBER, PA01201.PAcontid, PA01100.CUSTNMBR AS Expr1, PA01100.PAcontname, PA01100.PAcontid AS Expr2, PA01100.PACONTNUMBER AS Expr3, PA01201.PAprojname, PA01201.PAPROJNUMBER PA01201.Cost FROM PA01201 INNER JOIN PA01100 ON PA01201.CUSTNMBR = PA01100.CUSTNMBR AND PA01201.PACONTNUMBER = PA01100.PACONTNUMBER AND PA01201.PAcontid = PA01100.PAcontid </code></pre> <p>It returns these results:</p> <pre><code>CUSTNMBR PACONTNUMBER PAcontid Expr1 Pacontname Expr2 Expr3 Paprojname PAPROJNUMBER Cost AD001 8051 8051 AD001 Company 1 8051 8051 Project 1 08051P 951 AP001 8063 8063 AP001 Company 2 8063 8063 Project 2 08063P 2651 AR002 8096 8096 AR002 Company 3 8096 8096 Project 3 08096P 807 AR003 9033 9033 AR003 Company 4 9033 9033 Project 4 09033P 458 AS001 8013 8013 AS001 Company 5 8013 8013 Project 5 08013C 3297 AS001 8013 8013 AS001 Company 5 8013 8013 Project 5 08013P 48 AS001 8013 8013 AS001 Company 5 8013 8013 Project 5 08013P 641 AR002 8096 8096 AR002 Company 3 8096 8096 Project 3 08096P 1000 </code></pre> <p>I would like to be able to group the result by <code>PAcontid</code>.</p> <p>Can anyone advise the easiest way to do this?</p> <p>When I try and insert a <code>GROUP BY</code> clause I get an error stating that the other columns (CUSTNMBR, PAPROJNUMBER) etc are not included in the clause????</p> <p>Any help much appreciated.</p> <p>All work is being done in SQL Server Management Studio 2008 R2</p> <hr> <p>Sorry for some reason my last column (Cost) didn;t come through. I have updated my question to show the proper fields. Basically what I am trying to do is get a total cost for each Pacontid.</p> <p>I would like my results to look something like ........</p> <pre><code>CUSTNMBR PACONTNUMBER PAcontid Expr1 Pacontname Expr2 Expr3 Paprojname PAPROJNUMBER Cost AS001 8013 8013 AS001 Company 5 8013 8013 Project 5 08013P 3986 AD001 8051 8051 AD001 Company 1 8051 8051 Project 1 08051P 951 AP001 8063 8063 AP001 Company 2 8063 8063 Project 2 08063P 2651 AR002 8096 8096 AR002 Company 3 8096 8096 Project 3 08096P 1807 AR003 9033 9033 AR003 Company 4 9033 9033 Project 4 09033P 458 </code></pre> <p>Is this possible in what I am trying to do?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPankaj Jawale
    1. USuser1086159
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POGROUP BY confusion
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POGROUP BY confusion
      1. This table or related slice is empty.
      1. VTDownMod
    3. VO
      singulars
      1. POGROUP BY confusion
      1. This table or related slice is empty.
      1. VTDownMod
    1. COcan you add what you think the output should look like after this? i think maybe you are using the word 'group by' differently...
      singulars
      1. POGROUP BY confusion
      1. USRandy
    2. CO[SQL GROUP BY Statement](http://www.w3schools.com/sql/sql_groupby.asp)
      singulars
      1. POGROUP BY confusion
      1. USMagnus
    3. COWhat is your desired end result? You can't group by PAcontid because the other columns are different - there is nothing to really group together - GROUP BY allows you to group together rows with columns that have the same value, and aggregate other columns based on that grouping. For example, you might want to show the number of orders per account - SELECT PAcontid, COUNT(PACONTNUMBER) FROM Table GROUP BY PAcontid
      singulars
      1. POGROUP BY confusion
      1. USdash
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload