×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Rescue this newbie

Rescue this newbie

Rescue this newbie

(OP)
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)

RE: 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?
 

RE: Rescue this newbie

(OP)
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

RE: Rescue this newbie

(OP)
Indeed, it is DATETIME and it is exactly what I needed.

RE: Rescue this newbie

(OP)
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

RE: Rescue this newbie

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?
 

RE: Rescue this newbie

CODE

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

RE: Rescue this newbie

Excuse me, wrote my reply before finished reading.

Try something like:
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)


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

RE: Rescue this newbie

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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Rescue this newbie

(OP)
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

RE: Rescue this newbie

(OP)
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?

RE: Rescue this newbie

(OP)
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?

RE: Rescue this newbie

(OP)
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.

RE: Rescue this newbie

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

RE: Rescue this newbie

Anyway, what about this ?

CODE

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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Rescue this newbie

If you still have issue with uniqueness, you may try this:

CODE

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

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Rescue this newbie

(OP)
Hi Guys,

thanks for your input, really saved me there, the problem of uniqueness has been solved. But still I do have a question...

Is there any possibility to rewrite the following SQL statement.

SUM(CASE WHEN ... AND ... AND ... AND ... THEN 1 ELSE 0 END)

So I don't need to write lines for every exceptions I want to query from my database...
At the moment I have query that contain 600+ lines to query male/female at certain age, with certain education degree that like i.e. fruit or veggies etc...

Because after few hundreds of lines I get lost in the query.

Hope you can help me with it. And if another language should be applied like php or java-(script) to simplify this, than I am happy to hear it from you.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close