sql group by without aggregate function

Question

I have this query which is generated by application. It is not throwing any errors. I am wondering how the group by clause can work without having any aggregate functions in there. Can someone please exlpain.

SELECT customer.id AS customer_id,
   customer.firstname AS customer_firstname,
   customer.lastname AS customer_lastname,
   customer.skype_id AS customer_skype_id,
   customer.phone AS customer_phone,
   customer.punch_line AS customer_punch_line,       
FROM customer
JOIN accounts ON accounts.id = customer.id
LEFT OUTER JOIN customer_skills ON customer.id = customer_skills.customer_id
LEFT OUTER JOIN skills ON skills.id = customer_skills.skill_id
WHERE accounts.activated_at IS NOT NULL
  AND accounts.published_at IS NOT NULL
  AND (EXISTS
     (SELECT 1
      FROM customer_skills
      WHERE customer.id = customer_skills.customer_id
        AND skills.title IN (:title_1,
                             :title_2)))
GROUP BY customer.id,
     accounts.id
ORDER BY accounts.registered_at DESC

The group by essentially acts as a `distinct`. The reason why it's working is most probably because `customer.id` and `accounts.id` are the primary keys of the tables and therefor you are not required to include the other columns in the `group by` — , Nov 04 '14 at 07:21
Agree with @a_horse_with_no_name -- this is called *functional dependency*, and the only mentioned application of these (in the docs) are the primary keys: *A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.* http://www.postgresql.org/docs/current/static/sql-select.html#SQL-GROUPBY -- I think there is quite a few questions already about this feature in SO, so please browse them before refining your question, or closing it. — pozs, Nov 04 '14 at 08:15
possible duplicate of [Any reason for GROUP BY clause without aggregation function?](http://stackoverflow.com/questions/2197693/any-reason-for-group-by-clause-without-aggregation-function) — Claudio Floreani, Sep 29 '15 at 17:13

sql group by without aggregate function

0 Answers0