Reputation: 4797
I'm running the following query on Postgres
:
SELECT a.testscoreid,
COUNT(a.*) AS Questions,
a.created,
ROW_NUMBER() OVER (PARTITION BY Questions) AS Number,
b.irt_tlevel
FROM asmt.testscores a
JOIN asmt.questions b ON a.questionid = b.questionid
WHERE a.answered = TRUE
AND a.testscoreid = '53bd6ed5da2cfe7baf0d97ed'
AND irt_tlevel IS NOT NULL
GROUP BY a.testscoreid,
a.created,
b.irt_tlevel
ORDER BY Questions DESC,
a.created ASC
which gives me the following result:
Testscoreid Questions Created Number irt_tlevel
53bd6ed5da2cfe7baf0d97ed 272 2014-07-09 09:33:25.0 1 easy
53bd6ed5da2cfe7baf0d97ed 38 2014-07-09 09:33:25.0 1 hard
53bd6ed5da2cfe7baf0d97ed 34 2014-07-09 09:33:25.0 1 medium
I would like to have the Number
column increment each time, for all questionid
. I think I'm missing something in row_number()
.
I want the column Number to increment for all the questionid. For example - I would like to have 272 rows (with incrementing Number) for easy questions in the example above, 38 rows for hard and 34 rows for medium. In total, I would like to have (272+38+34) rows, with Number
incrementing as per Created
.
Any suggestions would be appreciated.
Upvotes: 1
Views: 140
Reputation: 110
Do you want to show (272 + 38 + 34) rows? What you expect is not clear. If you want such rows your main error is grouping with irt_tlevel level.
But if you do so to get the total count for a particular level you may have to have a sub query or a function call to get the total.
e.g.- (not tested for syntax errors)
SELECT a.testscoreid,
a.created,
ROW_NUMBER() OVER (ORDER BY created ) AS Number,
b.irt_tlevel irt_level
FROM asmt.testscores a, asmt.questions b
WHERE a.answered = TRUE
AND b.questionid = a.questionid
AND a.testscoreid = '53bd6ed5da2cfe7baf0d97ed'
ORDER BY a.created ASC
Upvotes: 1
Reputation: 1271241
Use ORDER BY
instead of PARTITION BY
:
SELECT a.testscoreid,
COUNT(*) AS Questions,
a.created,
ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS Number,
b.irt_tlevel
Upvotes: 1