Rescue this newbie

[vengelus82]

Hi Guys,

I am using SQL to categorize certain data.

28035 104
28035 30
28035 39
28035 75
28035 2
28035 86
28035 44
28035 84
28035 98
476 1
476 76
476 40
476 30
476 102
476 93
476 53
476 83
476 96
6697 76
6697 1
6697 41
6697 30
6697 102
6697 94
6697 83
6697 96

I want to select cells from the left column based on the multiple conditions from the right column

i.e. SELECT COUNT left_column FROM * WHERE right_column = 1 and 30.(Both conditions are mandatory)

 

[JarlH]

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?

 

[vengelus82]

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

 

[PHV]

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

 

[vengelus82]

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

 

[vengelus82]

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

 

[JarlH]

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]

SELECT EXTRACT(YEAR FROM users.date) AS Year
     , COUNT(CASE WHEN user_profile.profile_id = 1
                  THEN 'adam' ELSE NULL END ) AS Men
     , COUNT(CASE WHEN user_profile.profile_id = 2
                  THEN 'eve' ELSE NULL END ) AS Women
  FROM users 
INNER 
  JOIN user_profile 
    ON user_profile.user_id = users.id
GROUP 
    BY EXTRACT(YEAR FROM users.date)

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

 

[JarlH]

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.

 

[PHV]

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

 

[vengelus82]

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

 

[PHV]

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

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

 

[vengelus82]

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?

 

[vengelus82]

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?

 

[PHV]

Why are you using 2 instances of user_profile ?

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

 

[vengelus82]

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.

 

[PHV]

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

 

[vengelus82]

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

 

[PHV]

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

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

 

[PHV]

Anyway, what about this ?

SELECT EXTRACT(YEAR FROM users.date) AS Year,
       SUM(CASE WHEN table_gender.profile_id = 1 THEN 1 ELSE 0 END) AS "Men",
       SUM(CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 30 THEN 1 ELSE 0 END) AS "Men < 18 years",
       SUM(CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 31 THEN 1 ELSE 0 END) AS "Men 18 - 24 years",
       SUM(CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 32 THEN 1 ELSE 0 END) AS "Men 25 - 34 years",
       SUM(CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 33 THEN 1 ELSE 0 END) AS "Men 35 - 44 years",
       SUM(CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 35 THEN 1 ELSE 0 END) AS "Men 45 - 54 years",
       SUM(CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 36 THEN 1 ELSE 0 END) AS "Men 55 - 64 years",
       SUM(CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 37 THEN 1 ELSE 0 END) AS "Men 65+ years",
       SUM(CASE WHEN table_gender.profile_id = 2 THEN 1 ELSE 0 END) AS "Women",
       SUM(CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 30 THEN 1 ELSE 0 END) AS "Women < 18 years",
       SUM(CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 31 THEN 1 ELSE 0 END) AS "Women 18 - 24 years",
       SUM(CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 32 THEN 1 ELSE 0 END) AS "Women 25 - 34 years",
       SUM(CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 33 THEN 1 ELSE 0 END) AS "Women 35 - 44 years",
       SUM(CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 35 THEN 1 ELSE 0 END) AS "Women 45 - 54 years",
       SUM(CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 36 THEN 1 ELSE 0 END) AS "Women 55 - 64 years",
       SUM(CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 37 THEN 1 ELSE 0 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 users.id = table_birth.user_id
WHERE table_gender.profile_id IN (1,2) AND table_birth.profile_id IN (30,31,32,33,35,36,37)
GROUP BY EXTRACT(YEAR FROM users.date)

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