Rescue this newbie

Can be done in many ways. E.g.

1. Join the table with itself.

2. GROUP BY ... HAVING COUNT(*)

3. Subquery.

etc.

What have you tried so far?

thanks for your help. It was very helpful. I would like to post a follow-up....

I am doing this as follow, but can I somehow simplify this:

SELECT COALESCE(COUNT(DISTINCT table_gender.user_id), '0') AS Users -- Men 2008
FROM users INNER JOIN user_profile AS table_gender ON users.id = table_gender.user_id
INNER JOIN user_profile AS table_birth ON table_gender.user_id = table_birth.user_id
WHERE users.date LIKE '2008%' AND table_gender.profile_id = 1
UNION
SELECT COUNT(DISTINCT table_gender.user_id) AS Users -- Men 2009
FROM users INNER JOIN user_profile AS table_gender ON users.id = table_gender.user_id
INNER JOIN user_profile AS table_birth ON table_gender.user_id = table_birth.user_id
WHERE users.date LIKE '2009%' AND table_gender.profile_id = 1
UNION
SELECT COUNT(DISTINCT table_gender.user_id) AS Users -- Men 2010
FROM users INNER JOIN user_profile AS table_gender ON users.id = table_gender.user_id
INNER JOIN user_profile AS table_birth ON table_gender.user_id = table_birth.user_id
WHERE users.date LIKE '2010%' AND table_gender.profile_id = 1

to get the following result in the future:

Year | Men
2008 | 10
2009 | 110
2010 |2341
2011 | etc
2012 | etc

Assuming users.date is a real DATETIME column:
SELECT EXTRACT(YEAR FROM users.date) AS Year, COUNT(DISTINCT user_profile.user_id) AS Men
FROM users INNER JOIN user_profile ON users.id = table_profile.user_id
WHERE table_profile.profile_id = 1
GROUP BY EXTRACT(YEAR FROM users.date)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

Indeed, it is DATETIME and it is exactly what I needed.

I know this is lame

but how would you rewrite the following query if you would want to add a third column into it?

SELECT EXTRACT(YEAR FROM users.date) AS Year, COUNT(DISTINCT user_profile.user_id) AS Men
FROM users INNER JOIN user_profile ON users.id = table_profile.user_id
WHERE table_profile.profile_id = 1
GROUP BY EXTRACT(YEAR FROM users.date)

1 = Men 2 = Women

Year | Men | Women
2008 | 10 | 15
2009 | 110 | 140
2010 |2341 | 1800
2011 | etc | etc
2012 | etc | etc

The general GROUP BY "rule" is:
each column reference in the SELECT list must either identify a grouping column or be the argument of a set function.

Since you are currently grouping by year you have to specify which specific column 3 value you want for each year. Max, min or average, or perhaps something else?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

Excuse me, wrote my reply before finished reading.

Try something like:
[tt]SELECT EXTRACT(YEAR FROM users.date) AS Year,
COUNT(DISTINCT case when table_profile.profile_id = 1 then user_profile.user_id end) AS Men,
COUNT(DISTINCT case when table_profile.profile_id = 2 then user_profile.user_id end) AS Women
FROM users INNER JOIN user_profile ON users.id = table_profile.user_id
GROUP BY EXTRACT(YEAR FROM users.date)
[/tt]

Note that YEAR and DATE are both reserved words in ANSI/SQL.

SELECT EXTRACT(YEAR FROM users.date) AS Year
, SUM(CASE G.profile_id WHEN 1 THEN 1 ELSE 0 END) AS Men
, SUM(CASE G.profile_id WHEN 2 THEN 1 ELSE 0 END) AS Women
FROM users INNER JOIN (
SELECT DISTINCT user_id, profile_id FROM user_profile WHERE profile_id IN (1,2)
) G ON users.id = G.user_id
GROUP BY EXTRACT(YEAR FROM users.date)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

Could you elaborate what you mentioned? Quite confused...
Maybe because I am a n00b.

But because the second column of my table consist of id´s of several different items... I would like to divide the gender, ages etc in different columns

IF I would do the following, the result would be stack up vertically, instead of horizontally...

SELECT EXTRACT(YEAR FROM users.date) AS Year, COUNT(DISTINCT user_profile.user_id) AS Men
FROM users INNER JOIN user_profile ON users.id = table_profile.user_id
WHERE table_profile.profile_id = 1
GROUP BY EXTRACT(YEAR FROM users.date)
UNION ALL
SELECT EXTRACT(YEAR FROM users.date) AS Year, COUNT(DISTINCT user_profile.user_id) AS Women
FROM users INNER JOIN user_profile ON users.id = table_profile.user_id
WHERE table_profile.profile_id = 2
GROUP BY EXTRACT(YEAR FROM users.date)

What is the solution to this little problem? Would you like to elaborate too. So I don't feel like a ignorant kid... :S

Did you try my suggestion timestamped 22 Nov 10 7:58 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

Thank you all for your reply, and indeed I understand what happened in the queries you all mentioned.

My next question apply to the number of rows I have to insert for my query at the moment:

SELECT EXTRACT(YEAR FROM users.date) AS Year,
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 1 THEN table_gender.user_id END) AS Men,

COUNT(DISTINCT CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 30 THEN table_gender.user_id END) AS "Men < 18 years old",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 31 THEN table_gender.user_id END) AS "Men 18 - 24 years old",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 32 THEN table_gender.user_id END) AS "Men 25 - 34 years old",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 33 THEN table_gender.user_id END) AS "Men 35 - 44 years old",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 35 THEN table_gender.user_id END) AS "Men 45 - 54 years old",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 36 THEN table_gender.user_id END) AS "Men 55 - 64 years old",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 37 THEN table_gender.user_id END) AS "Men 65+ years old"
FROM users INNER JOIN user_profile AS table_gender ON users.id = table_gender.user_id
INNER JOIN user_profile AS table_birth ON table_gender.user_id = table_birth.user_id
GROUP BY EXTRACT(YEAR FROM users.date)

Althought this still works, my current number of rows is growing exponentially and you can bet that the server won't be able to keep this up or rather will require a lot of time to pull the data out of it. Is there any way to simplify this?

Hi Guys,
Thanks for all your suggestions. This is just a part of the whole query so far.

SELECT EXTRACT(YEAR FROM users.date) AS Year,
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 1 THEN table_gender.user_id END) AS "Men",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 30 THEN table_gender.user_id END) AS "Men < 18 years",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 31 THEN table_gender.user_id END) AS "Men 18 - 24 years",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 32 THEN table_gender.user_id END) AS "Men 25 - 34 years",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 33 THEN table_gender.user_id END) AS "Men 35 - 44 years",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 35 THEN table_gender.user_id END) AS "Men 45 - 54 years",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 36 THEN table_gender.user_id END) AS "Men 55 - 64 years",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 37 THEN table_gender.user_id END) AS "Men 65+ years",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 2 THEN table_gender.user_id END) AS "Women",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 30 THEN table_gender.user_id END) AS "Women < 18 years",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 31 THEN table_gender.user_id END) AS "Women 18 - 24 years",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 32 THEN table_gender.user_id END) AS "Women 25 - 34 years",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 33 THEN table_gender.user_id END) AS "Women 35 - 44 years",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 35 THEN table_gender.user_id END) AS "Women 45 - 54 years",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 36 THEN table_gender.user_id END) AS "Women 55 - 64 years",
COUNT(DISTINCT CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 37 THEN table_gender.user_id END) AS "Women 65+ years",
FROM users INNER JOIN user_profile AS table_gender ON users.id = table_gender.user_id
INNER JOIN user_profile AS table_birth ON table_gender.user_id = table_birth.user_id
GROUP BY EXTRACT(YEAR FROM users.date)

Is there a way to simplify this...
It cost enormous time to categorise it. Is there some way to improve this? So far the database crashes when I try to sort it all out... Could this be done through a Stored Procedures?

Why are you using 2 instances of user_profile ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

the table is self joined, the table consist of 2 columns, both are multi-key. the first column consist of the user and = multi-rows, the second column consist the different categories belonging to the particular user. by self joining it, I can categorise it. Like querying male or female from particular age-range etc.

But by doing this way, it get overflowed and I have no idea how to simplify it.

Get rid of the DISTINCT in the COUNTs function and add the following WHERE clause:
WHERE table_gender.profile_id IN (1,2) AND table_birth.profile_id IN (30,31,32,33,35,36,37)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

Well, it is quicker, but count every row up instead of counting any unique users

Even with the WHERE clause ?
Do you really have users with multiple gender and/or birth ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

Anyway, what about this ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886