selecting from the results of a previous select statement-CodePudding

I'm new to PostgreSql and trying to write a query that involves selecting from the results of a previous select, but cant seem to work the syntax out.

The subquery part(i.e. lines 2-11) of the following statement

WITH getJournalEbmStats AS (
    SELECT
        e.email AS EBM,
        er.status AS reviewStatus,
        count(er.status) AS reviewStatusCount
    FROM
        schemaName.editor_journals ej
        JOIN schemaName.editor e ON e.id = ej.editor_id
        JOIN schemaName.em_user eu ON eu.email = e.email
        JOIN schemaName.em_review_user eru ON eru.user_id = eu.id
        JOIN schemaName.em_review er ON er.id = eru.review_id
    WHERE
        ej."role" = 'someCondition'
        AND ej.stop_date IS NULL
        AND ej.journal_acronym = 'someOtherCondition'
    GROUP BY
        e.email,
        er.status
    ORDER BY
        e.email ASC,
        er.status ASC
)
SELECT
    EBM AS EBM,
    --(select reviewStatusCount from getJournalEbmStats where EBM = getJournalEbmStats.EBM and reviewStatus = 32) as completedReviewCount,
    --(select reviewStatusCount from getJournalEbmStats where EBM = getJournalEbmStats.EBM and reviewStatus = 256) as terminatedReviewCount,
    sum(reviewStatusCount) AS totalReviewCount,
    -- completedReviewCount replaces 100 below
    100 / sum(reviewStatusCount) AS percentCompleteReviews,
    -- terminatedReviewCount replaces 100 below
    100 / sum(reviewStatusCount) AS percentTerminatedReviews
FROM
    getJournalEbmStats
GROUP BY
    EBM
ORDER BY
    percentCompleteReviews DESC

generates a dataset that looks like the following

**EBM       reviewStatus    reviewStatusCount**
email1      16              88
email1      32              53
email1      128             2
email1      256             1
email1      512             2

email2      8               1
email2      16              20
email2      32              50
email2      128             18
email2      256             3

email3      4               1
email3      8               2
email3      16              11
email3      32              8
email3      512             1

which I'm then trying to select items from using the additional 2 other (currently commented out) subqueries to end up with a dataset that looks like

email1  [reviewStatusCount when reviewStatus = 32]  [reviewStatusCount when reviewStatus = 256] ...
email2  [reviewStatusCount when reviewStatus = 32]  [reviewStatusCount when reviewStatus = 256] ...
email3  [reviewStatusCount when reviewStatus = 32]  [reviewStatusCount when reviewStatus = 256] ...

but cant seem to work out the syntax required to achieve it. If I uncomment the either of the 2 commented out subqueries then I get the following error message:

SQL Error [21000]: ERROR: more than one row returned by a subquery used as an expression

Could anybody put me out of my misery and let me know what the error might be? Thanks in adanace

CodePudding user response：

SELECT
    EBM AS EBM,
    sum(reviewStatusCount) AS totalReviewCount,
    sum(reviewStatusCount ) filter where (reviewStatus = 32) AS completedReviewCount,
    sum(reviewStatusCount ) filter where (reviewStatus = 256) AS terminatedReviewCount,
    100 / sum(reviewStatusCount) AS percentCompleteReviews,
    100 / sum(reviewStatusCount) AS percentTerminatedReviews
FROM
    getJournalEbmStats
GROUP BY
    EBM
ORDER BY
    percentCompleteReviews DESC;

useful tutorial link: https://modern-sql.com/feature/filter

CodePudding user response：

For anyone who finds they have a similar problem, I eventually discovered the FILTER statement and solved the above using:

coalesce(sum(reviewStatusCount) filter (where reviewStatus = 32),0) as completedReviewCount,

CodePudding user response：

This is easy one but the question is not written well , but all good.

I have a question here , do you want the sum of your score of the count ?

either way it will be the best to do that in your sub query . and just write a one line condition to select your data .

the query will be as follow :

sum(reviewStatusCount) filter (where reviewStatusCount = 25)

count(reviewStatusCount) filter (where reviewStatusCount = 25)

if you need more description please ask and I will help you