Common Table Expression return Nothing but "command executed"

Asked Jan 24 '17 at 12:17

Active Jan 24 '17 at 12:48

Viewed 26 times

i have a user table, a pro table and a pro_users table to join : user -- many to many --> pro

i want to get the number of users, the number of pro joined to user AND the sum of user+member (this seams not relevant but it's just for testing)

i have this query :

WITH sums AS(
  SELECT COUNT(user.user_id) as usercount, COUNT(pro.pro_id) as procount
  FROM user
  LEFT JOIN pro_users ON user.user_id = pro_users.user_id
  LEFT JOIN pro ON pro_users.pro_id = pro.pro_id
)
SELECT usercount, procount, usercount+procount as total FROM sums

the inner select work as expected when queried alone. I've tried to only get members from the outter select and absolutely no result show up and i get

message:Command Executed

in PostgreSql Studio.

desired output:

| usercount | procount | total |  
--------------------------------  
|   1000    |    500   |  1500 |

What am i mising?

Thanks

edited Jan 24 '17 at 12:48

asked Jan 24 '17 at 12:17

Nils

what returns executed outside cte block? – McNets Jan 24 '17 at 12:18
Post sample data and desired output – Clodoaldo Neto Jan 24 '17 at 12:23
nothing but : `message:Command Executed` – Nils Jan 24 '17 at 12:23
@ClodoaldoNeto i've updated my question – Nils Jan 24 '17 at 12:30
1

Does it work with `psql` or another SQL client? – Jan 24 '17 at 12:34
@a_horse_with_no_name it works like a charm in psql ... Thanks! hope this will work in my app to – Nils Jan 24 '17 at 12:48
2

Sounds like a bug in PostgreSQL Studio then. You could rewrite your statement to `select usercount, procount, usercount+procount as total FROM ( .... ) as sums` to avoid the CTE – Jan 24 '17 at 12:54

Common Table Expression return Nothing but "command executed"

0 Answers0