0

Consider the query below:

SELECT DISTINCT ON (ser.id) * FROM server ser LEFT JOIN subscription sub ON ser.id = sub.server_id WHERE ( COUNT(SELECT err.id FROM error err WHERE ser.id = err.id) > 0 OR SUM(SELECT pay.amount FROM payment pay WHERE ser.id = pay.id) > 0 );

Here, a list of unique servers that are being subscribed to and that has errors or payments is returned.

However, instead of returning all server columns (*), I want to return the server id, the number of errors and the sum of payments. For example, the initial selection should look like this:

SELECT DISTINCT ON (ser.id) ser.id, countErrors, sumPayments

Selecting ser.id is straight forward, but how can countErrors and sumPayments be selected from the aggregate functions "count" and "sum" (considering that they are conditions in a WHERE clause)?

I imagined the "where" conditions would look something like this:

 COUNT(SELECT err.id FROM error err WHERE ser.id = err.id) AS countErrors > 0 OR SUM(SELECT pay.amount FROM payment pay WHERE ser.id = pay.id) AS sumPayments > 0

Is it possible to do this? If so, how can it be achieved?

Test data is shown below:

server +----+ | id | +----+ | 1 | +----+ | 2 | +----+ | 3 | +----+ | 4 | +----+ subscription +----+-----------+ | id | server_id | +----+-----------+ | 1 | 1 | +----+-----------+ | 2 | 2 | +----+-----------+ | 3 | 2 | +----+-----------+ | 4 | 3 | +----+-----------+ | 5 | 3 | +----+-----------+ error +----+-----------+ | id | server_id | +----+-----------+ | 1 | 1 | +----+-----------+ | 3 | 4 | +----+-----------+ payment +----+-----------+--------+ | id | server_id | amount | +----+-----------+--------+ | 1 | 1 | 200 | +----+-----------+--------+ | 2 | 2 | 200 | +----+-----------+--------+ | 3 | 2 | 100 | +----+-----------+--------+

Wanted result from test data:

+-----------+-------------+-------------+ | server_id | countErrors | sumPayments | +-----------+-------------+-------------+ | 1 | 1 | 200 | +-----------+-------------+-------------+ | 2 | 0 | 300 | +-----------+-------------+-------------+
  • Server#4 has no subscription, so it should be left out.
  • Server#3 has a subscription, but no errors or payments, so should be left out.
  • Server#1 and server#2 both have subscription and payments and/or errors.
Improve this question
6
  • 1
    Some sample data would be helpful here. Commented Feb 15, 2018 at 10:05
  • @TimBiegeleisen I'll play around with the answer that has been posted first to see if it works. Commented Feb 15, 2018 at 10:13
  • 1
    No, you should clarify your question with data for the benefit of all. Your question will not help many people without data. Commented Feb 15, 2018 at 10:13
  • @TimBiegeleisen Is the test data on correct format? Commented Feb 15, 2018 at 10:29
  • How does server #1 end up with a payment sum of 200, while server #2 has a sum of 300? Commented Feb 15, 2018 at 10:31

2 Answers 2

Reset to default
2

Unless I'm missing something, I would just write your query as follows. Perform the aggregation of errors and payments in separate bona fide subqueries, and join to them. Also, there is a join to the subscription table, but this only exists to filter off servers having no subscription. Finally, the WHERE clause removes any servers which do not either have some errors or payments.

SELECT s.id AS server_id, COALESCE(e.countErrors, 0) AS countErrors, COALESCE(p.sumPayments, 0) AS sumPayments FROM server s INNER JOIN ( SELECT DISTINCT server_id FROM subscription ) su ON s.id = su.server_id LEFT JOIN ( SELECT server_id, COUNT(*) AS countErrors FROM error GROUP BY server_id ) e ON s.id = e.server_id LEFT JOIN ( SELECT server_id, SUM(amount) AS sumPayments FROM payment GROUP BY server_id ) p ON s.id = p.server_id WHERE p.sumPayments > 0 OR e.countErrors > 0 ORDER BY s.id;

enter image description here

Demo

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

If "payment" got another column, lets call it "payment_type", and I want to return "sumPaymentTypeA", "sumPaymentTypeB", "sumPaymentTypeC" etc. Is the best practice to create a new JOIN-query for each "payment_type"? Or is it possible to do something more elegant within the same JOIN-query?
You might be able to just aggregate the sub payments inside the same subquery. But I recommend opening a new question if you need a different query.
I found a way to do it: "SUM(CASE WHEN payment_type = 0 THEN amount ELSE 0 END) AS sumPaymentTypeA" etc. It's simple and works perfectly fine. The question is just whether there is a more efficient/elegant way to do it or not. I assume this is fine enough though, so I'll stick with it unless if the universe starts to cry...
1

The mistake here is to put the COUNT outside of the SELECT, it needs to go inside:

(SELECT COUNT(err.id) FROM error err WHERE ser.id = err.id) > 0 OR (SELECT SUM(pay.amount) FROM payment pay WHERE ser.id = pay.id) > 0
Improve this answer

5 Comments

You mean like this: SELECT COUNT(err.id) AS countError FROM error err WHERE ser.id = err.id) > 0? At least this compiles. However, I get an error when I put SELECT DISTINCT ON (ser.id) ser.id, countErrors. It says that countErrors doesn't exist.
@MarcusPersson If you want to select that data, put the subselect in a JOIN instead of the where clause.
Right. However, if I have lets say, 10 WHERE conditions that I need to convert like this, that's a lot of joins. Is this a good solution performance wise?
@MarcusPersson It makes no difference, if anything the WHERE will be more costly. But the SQL engine should plan both the same way.
Ok. I'll try it out!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.