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)

 

[PHV]

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

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
FAQ181-2886

 

[vengelus82]

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.