Home » SQL & PL/SQL » SQL & PL/SQL » using case and group by [merged]
using case and group by [merged] [message #339528] Thu, 07 August 2008 15:10 Go to next message
aokwuadigb
Messages: 18
Registered: August 2008
Junior Member
I am trying to use the function case but not sure what I am missing.

Can I perform count like that in the CASE statement?
Do I need to pass in cost & target_id somehow?
Am I labeling the column names correctly?
Do i need to throw a GROUP BY in there?

Can someone please let me know where im going wrong with the following query?

SELECT location_id,
	CASE 
	WHEN  cost < 1 THEN count(target_id) ELSE 0
	END  '< 1',
	CASE  
	WHEN cost BETWEEN 1 AND 2.99 THEN count(target_id) ELSE 0
	END '1 - 2.99',
	CASE  
	WHEN cost BETWEEN 3 AND 4.99 THEN count(target_id) ELSE 0
	END '3 - 4.99',
	CASE  
	WHEN cost BETWEEN 5 AND 6.99 THEN count(target_id) ELSE 0
	END '5 - 6.99',
	CASE 
	WHEN cost >7 THEN count(target_id) ELSE 0
	END '>= 7'
FROM
	(
	SELECT
		*
	FROM
		targets t
	LEFT OUTER JOIN
		information i
	ON
		t.target_id=i.target_id
	LEFT OUTER JOIN
		access_cost ac
	ON
		i.security_level BETWEEN ac.lower_BOUND_LEVEL AND ac.upper_BOUND_LEVEL
	)


Re: Using CASE [message #339529 is a reply to message #339528] Thu, 07 August 2008 15:18 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Unless & until you follow Posting Guidelines, You're On Your own (YOYO)!
Re: Using CASE [message #339532 is a reply to message #339529] Thu, 07 August 2008 15:43 Go to previous messageGo to next message
aokwuadigb
Messages: 18
Registered: August 2008
Junior Member
Sorry,
I think I was way off with my initial code. The below query is partly what I am trying to achieve by the CASE statement.
I am trying to produce a table with the location and a count of the targets in each cost range.

SELECT   t.Location_Id,
         COUNT(ac.COST),
         COUNT(i.Target_Id)
FROM     Targets t
         LEFT OUTER JOIN Information i
           ON t.Target_Id = i.Target_Id
         LEFT OUTER JOIN Access_Cost ac
           ON i.Security_Level BETWEEN ac.Lower_Bound_Level
                                       AND ac.Upper_Bound_Level
WHERE    COST BETWEEN 2
                      AND 4.99
GROUP BY t.Location_Id

Is it the formating you were refering to?

[Updated on: Thu, 07 August 2008 15:57]

Report message to a moderator

Re: Using CASE [message #339536 is a reply to message #339528] Thu, 07 August 2008 15:58 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Which items in the Posting Guidelines have you followed?
Please enumerate any/all such items.
Re: Using CASE [message #339537 is a reply to message #339532] Thu, 07 August 2008 16:04 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The problem is that the question can not be answered.

You ask what "what is going wrong with the query" and you post a query saying "this is what I'm trying to achieve."

- We don't know your table model
- We don't know your data
- We don't know what you actually DO try to get as a result.

So we need at least some sample data (best with CREATE TABLE and INSERT statements so that we can work with that) and an example of what result you actually need.
Re: Using CASE [message #339538 is a reply to message #339536] Thu, 07 August 2008 16:05 Go to previous messageGo to next message
aokwuadigb
Messages: 18
Registered: August 2008
Junior Member
Be polite!




Never belittle anyone for asking beginner-level questions or for their English skills.

Use English language in the main forum (non-English forums are also provided - see bottom of list) and DO NOT use IM-speak!

Provide all relevant information about your problem, including Oracle software version (4 decimal places) and operating system version.

Format your code and make sure that lines do not exceed 80 characters. Use the "Preview Message" button to check it.
Re: Using CASE [message #339540 is a reply to message #339528] Thu, 07 August 2008 16:15 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
By the way, column alias need to be enclosed is double quote marks;
not single quote marks as duly noted for anyone who took the time & effort to Read The Fine Manual.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/expressions002.htm

SELECT ITEM "< 1" FROM PRODUCT; -- IS VALID SQL
SELECT ITEM '< 1' FROM PRODUCT; -- generates error
Using CASE & GROUP BY [message #339726 is a reply to message #339528] Fri, 08 August 2008 04:20 Go to previous messageGo to next message
aokwuadigb
Messages: 18
Registered: August 2008
Junior Member
I am trying to right a query that displays a location with the number of targets within each price range from a table I have created which looks like.
TARGET_ID        COST     LOCATION_ID  
22                6.76                1   
83               13.79               1  
256              6.71                1  
50                6.76                2  
127              13.52               2  
186              12.07               2    
8                  6.71               3  
200              17.33               3  
48                13.79               4  


The code is not counting the targets in each location it just counts the targets and groups them in one location.
Can anyone explain why this is happening?
SELECT   Location_Id,
         CASE 
           WHEN SUM(COST) < 1 THEN COUNT(Target_Id)
           ELSE 0
         END "< 1",
         CASE 
           WHEN SUM(COST) BETWEEN 1
                                  AND 2.99 THEN COUNT(Target_Id)
           ELSE 0
         END "1 - 2.99",
         CASE 
           WHEN SUM(COST) BETWEEN 3
                                  AND 4.99 THEN COUNT(Target_Id)
           ELSE 0
         END "3 - 4.99",
         CASE 
           WHEN SUM(COST) BETWEEN 5
                                  AND 6.99 THEN COUNT(Target_Id)
           ELSE 0
         END "5 - 6.99",
         CASE 
           WHEN SUM(COST) > 7 THEN COUNT(Target_Id)
           ELSE 0
         END ">= 7"
FROM     (SELECT t.Target_Id Target_Id,
                 ac.COST COST,
                 t.Location_Id Location_Id
          FROM   Targets t
                 INNER JOIN Information i
                   ON t.Target_Id = i.Target_Id
                 LEFT OUTER JOIN Access_Cost ac
                   ON i.Security_Level BETWEEN Nvl(ac.Lower_Bound_Level,i.Security_Level)
                                               AND Nvl(ac.Upper_Bound_Level,i.Security_Level))
GROUP BY Location_Id
HAVING   COUNT(Target_Id) >= 3
ORDER BY Location_Id
Re: Using CASE & GROUP BY [message #339729 is a reply to message #339726] Fri, 08 August 2008 04:37 Go to previous messageGo to next message
aokwuadigb
Messages: 18
Registered: August 2008
Junior Member
Results that I get which count targets in just one range, are attached.

  • Attachment: results.jpg
    (Size: 62.72KB, Downloaded 103 times)
Re: Using CASE & GROUP BY [message #339730 is a reply to message #339726] Fri, 08 August 2008 04:45 Go to previous messageGo to next message
aokwuadigb
Messages: 18
Registered: August 2008
Junior Member
Can I put a WHERE clause between the THEN and ELSE statement, so I will have
SELECT location_id,
	CASE 
	WHEN  sum(cost) < 1 THEN count(target_id) where sum(cost) < 1 ELSE 0

instead of
SELECT location_id,
	CASE 
	WHEN  sum(cost) < 1 THEN count(target_id) ELSE 0
Re: Using CASE & GROUP BY [message #339734 is a reply to message #339730] Fri, 08 August 2008 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No you can't.
Please have a look at SQL Reference, the syntax is fully described.

Explain what do you want to do with the following:
         CASE 
           WHEN SUM(COST) < 1 THEN COUNT(Target_Id)
           ELSE 0
         END "< 1",

Regards
Michel
Re: Using CASE & GROUP BY [message #339737 is a reply to message #339734] Fri, 08 August 2008 05:23 Go to previous messageGo to next message
aokwuadigb
Messages: 18
Registered: August 2008
Junior Member
I am trying to count all the targets who have total costs less than 1, for each location.

Maybe I need to rap the whole thing in another SELECT statement and then GROUP BY location_id and have the SUM of each column for each location.

I trie this and I am getting an error:

SELECT loc, sum('<1') FROM (
*

ERROR at line 1:
ORA-01722: invalid number


SELECT   Loc,
         SUM('<1')
FROM     (SELECT   Location_Id Loc,
                   CASE 
                     WHEN COST < 1 THEN COUNT(Target_Id)
                     ELSE 0
                   END "< 1",
                   CASE 
                     WHEN COST BETWEEN 1
                                       AND 2.99 THEN COUNT(Target_Id)
                     ELSE 0
                   END "1 - 2.99",
                   CASE 
                     WHEN COST BETWEEN 3
                                       AND 4.99 THEN COUNT(Target_Id)
                     ELSE 0
                   END "3 - 4.99",
                   CASE 
                     WHEN COST BETWEEN 5
                                       AND 6.99 THEN COUNT(Target_Id)
                     ELSE 0
                   END "5 - 6.99",
                   CASE 
                     WHEN COST > 7 THEN COUNT(Target_Id)
                     ELSE 0
                   END ">= 7"
          FROM     (SELECT   t.Target_Id Target_Id,
                             SUM(ac.COST) COST,
                             t.Location_Id Location_Id
                    FROM     Targets t
                             INNER JOIN Information i
                               ON t.Target_Id = i.Target_Id
                             LEFT OUTER JOIN Access_Cost ac
                               ON i.Security_Level BETWEEN Nvl(ac.Lower_Bound_Level,i.Security_Level)
                                                           AND Nvl(ac.Upper_Bound_Level,i.Security_Level)
                    GROUP BY t.Target_Id,
                             t.Location_Id
                    ORDER BY Target_Id)
          GROUP BY Location_Id,
                   COST
          ORDER BY Location_Id)
GROUP BY Loc
ORDER BY Loc
Re: Using CASE & GROUP BY [message #339738 is a reply to message #339737] Fri, 08 August 2008 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you know SCOTT and its EMP table?
Use it, translate your problem to this table and explain it.


Regards
Michel
Re: Using CASE & GROUP BY [message #339739 is a reply to message #339738] Fri, 08 August 2008 05:41 Go to previous messageGo to next message
aokwuadigb
Messages: 18
Registered: August 2008
Junior Member
Sorry dont know what What SCOTT or EMP is.
Re: Using CASE & GROUP BY [message #339742 is a reply to message #339739] Fri, 08 August 2008 05:57 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
aokwuadigb wrote on Fri, 08 August 2008 12:41
Sorry dont know what What SCOTT or EMP is.

http://www.orafaq.com/forum/m/288368/102589/?#msg_288107

Regards
Michel

Previous Topic: executing a function which returns boolean
Next Topic: Trigger
Goto Forum:
  


Current Time: Sun Dec 11 08:02:52 CST 2016

Total time taken to generate the page: 0.08535 seconds