CODEWITHSUNDEEP
phpsqlgroup-bycase
beck777
beck777

Reputation: 49

php SQL: Select AND Group results according to different fields, depending on criteria

I am creating a table to display a summary of many different projects, some of which are by country and some by region (multiple regions for one country), but there are only 2 countries that have multiple regions (where ADMIN0ID = 23 or 24)... so in those cases, I need to select and group results based on ADMIN1. The hierarchy of the source table is ADMIN0ID, ADMIN0, ADMIN1ID, ADMIN1, ADMIN2ID, ADMIN2, where anything ending in "ID" is numeric but the corresponding field without "ID" is text. Here is my rudimentary attempt to get the Project names, without any of the summary data:

SELECT 
(CASE WHEN (ADMINOID=24 OR ADMIN0ID=23) THEN ADMIN1 ELSE ADMIN0) AS Project
FROM GTMP_PROGRESS
GROUP BY
(CASE WHEN (ADMIN0ID=24 OR ADMIN0ID=23) THEN ADMIN 1 ELSE ADMIN0)

Please help me!

Upvotes: 1

Views: 32

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Is this the query you want?

SELECT (CASE WHEN ADMIN0ID in (23, 24) THEN ADMIN1 ELSE ADMIN0 END) AS Project
FROM GTMP_PROGRESS
GROUP BY (CASE WHEN ADMIN0ID in (23, 24) THEN ADMIN1 ELSE ADMIN0 END);

If so, the primary problem was the lack of end in the case statement.

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562659

I'm guessing that you got some error about "unknown column" or something. In the future, when you ask a question, it would be helpful to be specific about any errors you got, or wrong query results, and how that's different from what you expected to get.

I see at least this problem in your query:

Zero (0) is different from the capital letter 'O'. You're using 'O' in one place where you should have 0.

... CASE WHEN (ADMINOID=24 OR ADMIN0ID=23 ...
                    ^              ^
                    O              0

Upvotes: 1

Related Questions