I have a table users(id and name) and order(cost and user: FK to users).

users.name users.id
bob               1
alice             1


orders.user orders.cost
          1          15
          1           5
          2           4
          1           4
          1           3
          1           3
          2           3

Here is a query which joints users and orders and sorts by order cost.

users.name users.id orders.user orders.cost
bob               1           1          15
alice             1           1           5
bob               2           2           4
alice             1           1           4
alice             1           1           3
alice             1           1           3
bob               2           2           3

If I were to do a distinct on user (in Postgresql), I would get

users.name    cost
bob              15
alice             5

I actually want the counts, while still being sorted on highest order cost/

users.name    count
bob               3
alice             4
  • Are we talking about a single table foo or two tables users and order in a 1:n relationship like in the accepted answer? Not the same thing. And different caveats, too. Actual table definitions showing data types and constraints and your version of Postgres are the way to go. – Erwin Brandstetter Mar 26 '17 at 4:07
  • @ErwinBrandstetter a high-rep user edited my question assuming a single table. I did mean it for joined tables. – Jesvin Jose Mar 27 '17 at 14:53
  • I see, that was a misleading edit. Now, if you consider the rest of my comment: Actual table definitions showing data types and constraints and your version of Postgres are the way to go. Table definitions include data types and constraints, like a complete CREATE TABLE statement. And in case you are not aware of your Postgres version: SELECT version(); – Erwin Brandstetter Mar 27 '17 at 22:59
  • @ErwinBrandstetter I will not post undetailed questions going forward. I use an ORM and migration manager so I am rusty in both DDL and SQL. Is there something to help? – Jesvin Jose Mar 28 '17 at 6:22
  • pgAdmin shows complete DDL scripts. Or provide the output of \d+ tablename in psql (not a CREATE TABLE script, but also complete information). – Erwin Brandstetter Mar 28 '17 at 12:09

Not sure I understand, but I guess something like:

select u.name, count(*)
from users u
join order o
    on u.id = o.user
group by u.name
order by max(o.cost) desc

would do. Please include your query and if relevant, table definitions for future questions.

  • Could you explain the order by? I'm ordering on highest unique cost. I'm trying it out myself. – Jesvin Jose Mar 24 '17 at 6:49
  • I tried max() for order by and it seems to work by my observations. – Jesvin Jose Mar 24 '17 at 7:04
  • A desc to the order by is also necessary. – Jesvin Jose Mar 24 '17 at 7:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.