CODEWITHSUNDEEP
mysql
user3142695
user3142695

Reputation: 17332

Nested MySQL-Selection

A have a table with rows which consists invoice data and person data to which these belong.

Something like

name, birthday, sex, invoice_date, invoice_amount

First I want to get all datasets for every unique person. So I do a select like this

SELECT * FROM data WHERE invoice_amount < 10 GROUP BY name, birthday

With this I filter all duplicate entries for the same persons.

Now I want to know how many of these persons are female - not all rows, but the grouped result. I mean I want to make a select from the results (I don't think of WHERE invoice_amount <10 AND sex = 1). How do I do that?

So how do I do a nested selection for that example case?

Upvotes: 0

Views: 17

Answers (1)

fthiella
fthiella

Reputation: 49049

You can always use a subquery:

SELECT sex, count(*)
FROM (
  SELECT * FROM data WHERE invoice_amount < 10 GROUP BY name, birthday
) s
GROUP BY sex

but please notice that is not considered good practice to SELECT all fields, and group by fewer fields without using aggregated functions. I would better write your query this way:

SELECT sex, COUNT(DISTINCT name, birthday)
FROM data
WHERE invoice_amount < 10
GROUP BY sex

Update

The table is not normalized so it can happen that the same person (same name and same birthday) has different sex information on different rows.

You have two options here. You can just ignore the problem, a few mistakes in a big dataset won't (probably) make much difference, so you can use the query with a subquery.

If you SELECT * but only use GROUP BY name, birthday, you will get just one row for each person with one value for sex (if there are multiple values, the one you will get is unpredictable, but it can be fine on a huge dataset).

But if you want to identify which records are wrong you can use this:

SELECT name, birthday
FROM data
GROUP BY name, birthday
HAVING COUNT(DISTINCT sex)>1

Upvotes: 1

Related Questions