sum() i Access

Det kommer an på hvad du ønsker, men du kan ikke hendte andet ud en summen untagen end hvis du har en GRUOP BY ColorName:

SELECT Sum(Total) As ColorTotal, ColorName FROM Colors GRUOP BY ColorName

sum is an aggregate function try grouping by ColorName

too slow I see :o) BUT there is a little error there :o)

SELECT Sum(Total) As ColorTotal, ColorName FROM Colors GROUP BY ColorName

Or just
SELECT Sum(Total) As ColorTotal FROM Colors

;) UO -> OU

SELECT colour.colourname, Count(colour.colourname) AS AntalOfcolourname
FROM colour
GROUP BY colour.colourname;

Hi mugs>Count and sum dont produce the same result :o)

Hi terry > No I can see that I have some difficulties. From now on I won't have anything to do with SQL. That's just like the Chinese language, so I will go in My garden again and work in My Greenhouse. I know a little about that :o)

Your doing great mugs. SQL isnt my hot spot either, what would I do without the query builder :o)

hvad gør group by??

jeg kan ikke få det til at virke. Er det meget mere krævende for serveren at gøre således:

    rs.Open "SELECT ColorName, Total FROM Colors", , , adCmdTable
    ColorTotal = rs("Total")
    Do Until rs.eof
        ColorTotal = ColorTotal + rs("Total")
        rs.movenext
    loop
    rs.movefirst

example

Total - ColorName
2        Red
1        Red
4        Black

SELECT Sum(Total) As ColorTotal, ColorName FROM Colors GROUP BY ColorName

would give

Total - ColorName
3        Red
4        Black

It Sums the Total up for each color

Den grupper så den tæller recordset så alle med samme navn kommer ud sammen og man kan summere en kolonne for alle hvor de har ens navne.

Men hvis du tælle det hele sammen så det bare:

"SELECT sum(total) as sumTotal FROM colors"

actually it would give
ColorTotal - ColorName :o)

Ja/nej det belaster serveren. Jo flere records du har jo mreer belaster det.

og så får du den føste record talt med 2 gange fordi du skriver sådan her:
ColorTotal = rs("Total")

Det skal være:
ColorTotal = 0

moejensen>Your comment 10/06-2003 20:05:20 This IS NOT the same question you asked originally.

--> terry. no, its not the same

--> problemet er at jeg både skal bruge summen af dem alle, men også hver enkelt record.

så jeg skal bruge Total, ColorName og en sum af alle total

and all that for 5 points :o)

SELECT tbl1.ColorName , (SELECT Sum(tbl1.Total)
FROM tbl1;
) AS SumTot
FROM tbl1;

Så skal du enten lave det i to SQL sætninger som eller som du har vist i kommentar 10/06-2003 20:05:20

    rs.Open "SELECT sum(Total) FROM Colors", , , adCmdTable
    sumTotal = rs(0)
    rs.Close
    rs.Open "SELECT ColorName, Total FROM Colors", , , adCmdTable
    Do Until rs.eof
        'Hvad du nu skal med det her..
        rs.movenext
    loop

replace tbl1 with Colors :o)

Now I'm off to see Jack is Back :o)

NOTE: You will get all ColorName and the total in ALL records, thats about as close as you can get

okay, takker :-)