CODEWITHSUNDEEP
mysqlsqlgroup-byaggregate-functions
Stas Bichenko
Stas Bichenko

Reputation: 13283

Can I use aggregate functions and get individual results in one query with MySQL?

I have an apartment table with many columns. Some apartments share price and idlayout column values. However, they still have different values for area (shoddy workmanship).

I need to:

1) Group the apartments with same idlayout and price, get their min and max area and count the number of apartments in each group.

2) List individual apartments for each group.

Can this be done with one query and should I even be trying to do so?

What I tried:

I got the query for the first part, but I can't come up with a way to list the apartments in the result set.

SELECT COUNT( * ), price, rooms, MAX( area ) AS areaMax, MIN( area ) AS areaMin
FROM apartment
GROUP BY price, layout_idlayout
ORDER BY rooms ASC, price ASC

Upvotes: 0

Views: 973

Answers (2)

Andomar
Andomar

Reputation: 238296

You can use group_concat to list all values in a group:

SELECT  layout_idlayout
,       price
,       group_concat(name) as ListOfNames
,       MAX(area) as MaxArea
FROM    apartment
GROUP BY 
        price
,       layout_idlayout

Upvotes: 2

fthiella
fthiella

Reputation: 49089

You need to use a JOIN:

SELECT apartment.*, areaMax, areaMin, cnt
FROM
  apartment INNER JOIN (
    SELECT
      idlayout, price, MAX(area) AS areaMax, MIN(area) AS areaMin, COUNT(*) cnt
    FROM
      apartment
    GROUP BY
      price, layout_idlayout) ap_grp
  ON apartment.idlayout=ap_grp.idlayout AND apartment.price=ap_grp.price

This will show all apartment data, along with the MAX, MIN and COUNT for other apartments that have the same price and idlayout.

Upvotes: 1

Related Questions