SQL Query with SUM and Groupby

[PM312]

Hi,

I Have below table

I want all rows with all coloumn from below table where ActCode is 1162828 and sum of DocAmount for DocNo. i.e where the docNo is repeated i want only one row with sum of amount of all repeated rows.

**

"Select *, Sum (DocAmount)  from TblPurLedTranData  whre TblPurLedtranData.ActCode='" & 1162828 & "' Groupby DocNo "

**

PosDate     DocDate     DocNo         DocAmount     DueDate    Status       Period     DocType    FinYear       Chan          ActCode
10/10/2018  12/10/2018  369896       36980          11/11/2018  INV         5          PUST         2018        PUST        1243684   
10/10/2018  12/12/2018  665          36000          11/01/2019  INV         5           CR          2018       PUST       1162828   
10/10/2018  10/05/2018  3620         25000          09/06/2018  INV         5           INV         2018        PUST        1162828   
10/10/2018  10/05/2018  3620         25000          09/06/2018  INV         5           INV         2018        PUST        1162828   
10/10/2018  10/05/2018  3620         -69800         09/06/2018  INV         5           INV         2018        PUST        1162828   
10/10/2018  10/05/2018  6500         10200          09/06/2018  INV         5           INV         2018        PUST        1162828   
10/10/2018  10/05/2018  9988         35600          09/06/2018  INV         5           INV         2018        PUST        1162828   

Thanks

[pty]

Excluding your aggregate columns you need to group by all you select by. That's just the way it works.

[Kim Rowden]

PM312, I noticed your question and it looks like you are asking for 2 things:

1. the sum of the 'DocAmount' - that's as explained by 'pty' in the other reply.

2. you want to display other info about the 'DocNo's that you are using in the GROUP BY. This is a very different request from the SUM(). If all the other columns will have the same data, for all rows with, say, DocNo = 3620 (as in your example), then those columns should really be in a parent table.

I'm guessing at your table schema... but I might set up a parent table called 'Doc' which contained PosDate, DocDate, FinYear, ActCode - and, of course, the Doc No as a unique Primary Key.
Then a second table, Payment', might be the payments: containing a primary key of some sort, the 'DocAmount' and any other data that only applies to this Doc payment - and then, of course, the Doc No as a Foreign Key (pointing back to the relevant entry in the 'Doc' table).

You can then run through the Doc rows one by one (and show the static Doc-related data), then as you get each Doc row you can can run a second query to get the SUM(DocAmount) for that DocNo. Then you move on to the next Doc row etc...

You don't say how or where you are running these queries. In a program it is easy to break up the queries. In a SQL command prompt environment it can be done (in one table as you first laid it out - or in two tables per my suggestion) but you'll need to create a view and run a nested query - more complicated and the formatting is tougher.

Let me know if this works for you... I'm guessing at much of the above so I may be off target - but I'm trying to read between the lines.
-Kim

[scamquist]

check your spelling. You used whre, not where

[SeanDevoy]

As mentioned, you are very close. The part it seems you don't understand is that for "aggregate functions" (like SUM, MAX, MIN and others) the rules for "SELECT" columns and "GROUP BY" columns are necesarilly quite strict. Every column in the "SELECT" fields must either be an aggregated column or be included in the "GROUP BY" section.
Some examples:

Select a, b, c, sum(d) from XYZ group by a, b, c;

or

Select a, b, SUM(c), SUM(d) from XYZ group by a, b

or

Select a, b, MIN(c), MAX(d), SUM(e) from XYZ group by a, b

so your's would be:

select PosDate,DocDate, DocNo,  DueDate, Status,Period, 
    DocType, FinYear, Chan, ActCode, SUM(DocAmount)
from TBLPureLedTranData 
where AcctCode = '1162828'
Group by PosDate,DocDate, DocNo,  DueDate, Status,Period, 
    DocType, FinYear, Chan, ActCode

HTH,
Sean