We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

BASE - Column in query as cumulative value

asked 2016-07-27 20:01:57 +0200

Alb58 gravatar image

I am lacking in SQL programming knowledge and would like to know how to generate a query which adds up the values of the previous rows. For example the first column would be something like grade school levels from 1st grade to 6th grade. The second column is number of students enrolled in that grade. These two columns are in a table. The third column which is the one I'm having trouble with is how many students in 1st grade, followed in the next row by how many students enrolled in 1st and 2nd grade, the third row adds all students from 1st to 3rd grade, etc. I want to generate a query that has all three of these columns. Any help will be appreciated.

edit retag flag offensive close merge delete


Your question has a number of contradictions. Your final statement specifies three columns but the explanation has five and mixes in ROWS. Could you please edit your original question showing what result you are looking for?

Ratslinger gravatar imageRatslinger ( 2016-07-27 20:40:45 +0200 )edit

Sorry that I wasn't clear. I tried to edit but don't find how. Here's an example of what I'd like. I have a table with the first 2 columns. I want to make a query that will add the third one w/ cumulative amounts. |Grade | Number of Students | Cumulative Number of Students | |1st Grade | 5 | 5 | |2nd Grade | 7 | 12 | |3rd Grade | 6 | 18 | |4th Grade | 5 | 23 |

Alb58 gravatar imageAlb58 ( 2016-07-27 22:14:47 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2016-07-28 00:53:27 +0200

Ratslinger gravatar image

There is most likely a better way to accomplish what you want, but this works:

image description

The UNION statements create total records which are joined to the original selection records. Obviously, the GRADENUMBER values are the critical part. The table in the upper right corner represents the table you already have.

edit flag offensive delete link more


Hi @Ratslinger
This is to give you a heads-up on a possible corruption affecting your personal profile.
I've been spending months going through very old Questions & closing them. Very occasionally I will tick an Answer that is obviously correct but not chosen by the OP (like the one above, although that one is an edge case!). The pages that I'm working through have 30 Q. I return to that page after closing a Q, then refresh. My discovery is that, very occasionally, a selected A will de-select itself before I get through all 30 Q, and I will need to go through the whole process with that Q&A all over again. That once happened 3 times with the same Q&A.
Here is the point. Gradually, it became apparent to me that ALL of those problematic Q were replied to by you. It has not happened with every ...(more)

Alex Kemp gravatar imageAlex Kemp ( 2020-09-09 22:43:19 +0200 )edit

@Alex Kemp,

Don't see any problem. Have stated before that I did not think what you were doing was correct. Just got a snide remark back when noted. As stated before - Do what you want.

Ratslinger gravatar imageRatslinger ( 2020-09-09 22:50:30 +0200 )edit

Dear lord. This is nothing to do with me, and also unconnected with your attitude towards me.
It seemed to me that the activity that I saw suggested that your profile may be corrupted on the server. If true, you obviously needed to know about that. I've informed you, and obviously you may ignore it if you wish.

Alex Kemp gravatar imageAlex Kemp ( 2020-09-09 23:25:37 +0200 )edit

@Alex Kemp stated:

This is nothing to do with me, and also unconnected with your attitude towards me.

Of course this is about you. Why else do you explain what you are doing. Only attitude is not to agree with what you are doing. Have seen corrupt profiles happen way too often with this site.

Will now continue to try to ignore as I was doing in the past.

Ratslinger gravatar imageRatslinger ( 2020-09-09 23:58:54 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-07-27 20:01:57 +0200

Seen: 200 times

Last updated: Jul 28 '16