Having "HAVING" problems with GROUP BY.

Having "HAVING" problems with GROUP BY.

Post by Joseph Ranset » Thu, 07 Sep 2000 00:59:09



My query is quite complex, and it also not working properly! *g*
From 2 tables, I am selecting records grouped by player_id where a certain
number of conditions are met (ie: date_caught, etc.)
I need to perform 2 GROUP BYs.  1 to ensure that the SUM of the
actual_points is within a certain value, and the 2nd to ensure that each
player has at least 4 entries.

I am only choosing the top four, which isn't a problem for players with 4 or
more entries, but the group by is NOT working for players who have less than
4 entries.  It is NOT leaving out the players with 3 or less
entries......ANY HELP WOULD BE GREATLY APPRECIATED.

Here's the SQL (i've tried to snip it as much as I could....):

SELECT player_id , ROUND(SUM(actual_points)) as points FROM ( SELECT * FROM
(SELECT player_id
, actual_points
, row_number() OVER (PARTITION BY player_id ORDER BY actual_points desc)
rank
, date_caught
FROM (SELECT *
FROM (SELECT ce.entry_id
, ce.date_caught as date_caught
, ce.actual_points as actual_points
, ce.player_id as player_id
FROM fantasy_entries ce )
UNION
SELECT *
FROM( SELECT ce.entry_id
, ce.date_caught as date_caught
, ce.actual_points as actual_points
, players.player_id as player_id
FROM catch_entries ce, dogfish.players
WHERE ce.pin = players.pin )
)
)
WHERE rank < 5
AND player_id IN ( SELECT *
FROM (SELECT player_id
FROM fantasy_entries
UNION
SELECT *
FROM (SELECT player_id
FROM (SELECT pl.player_id as player_id
, ce.pin
FROM players pl
, catch_entries ce
WHERE pl.pin = ce.pin)
)
)
GROUP BY player_id HAVING COUNT(actual_points) = 4) /*(when this is '> 0',
it works okay, it just doesn't filter the players with more than 4
entries)*/
)
GROUP BY player_id HAVING SUM(actual_points) BETWEEN 0 AND 2800
ORDER BY points desc

Thanks in advance....I really appreciate any insight.  (I've been fighting
with this for a week!)
--
Joseph Ranseth - Webmaster
World Cup Fishing
http://www.worldcupfishing.com

 
 
 

Having "HAVING" problems with GROUP BY.

Post by Cona » Thu, 07 Sep 2000 02:57:40


Hi Joseph,
    I read through your query and I'm not sure I know exactly what you're
trying to-do, but here is my best attempt at answer

select * from (
select player_id , ROUND(SUM(actual_points)) points
from (
   SELECT
      ce.entry_id,
      ce.date_caught as date_caught,
      ce.actual_points as actual_points,
      ce.player_id as player_id
   FROM
      fantasy_entries ce
   UNION all
   SELECT
     ce.entry_id,
     ce.date_caught as date_caught,
     ce.actual_points as actual_points,
     players.player_id as player_id
   FROM
      catch_entries ce,
      dogfish.players
   WHERE ce.pin = players.pin ) rs1
group by
   player_id
having
   SUM(actual_points) BETWEEN 0 AND 2800
   and count(playerid) > 3
order by points desc)
where rownum < 5
/

AFAIK the order by in an inline view is only supported from version 8i
onwards, if im on the right track but you need the syntax for an earlier
version send me an E-mail

HTH

Conan

 
 
 

1. Having problems with "Insert Into" command

Hi, I'm using VB 4.0 professional 32 bit version and I'm trying to
create a databse with a few tables.  Now I have no problem making the
tables but I'm having trouble filling the tables with data.  What I have
right now is a table (called Sounds) which has two fields.  When I
specify both field names in my SQL Insert Into statement it accepts the
data that I give it.  However, if I leave out a field name in the table
then it gives me an error.  I thought that any fields left out of a
record are skipped and Nulls are stored into them.  I have provided the
sample code I use to enter the information into the database.  Can
someone tell me why I need to specify all the field names in a table
when I use the Insert Into command?

The sql_com' statement below does not work unless I also specify the
other field in this table, i.e.

sql_com="INSERT INTO Sound(Music_Type,Group) VALUES(""Type A"",""Group
A"")"

Why is this?


Private Sub Command2_Click()

    Dim Wrkspace As Workspace
    Dim Music_DB As Database
    Dim NewTable As TableDef
    Dim rec_list As Recordset
    Dim sql_com As String

    'Set aside the workspace region for the database
    Set defwrkspace = DBEngine.Workspaces(0)

    'Now open the existing database
    Set Music_DB = defwrkspace.OpenDatabase("c:\dosvol\work\vbasic\database\newdb.mdb")

    'Insert some data into the database
    sql_com = "INSERT INTO Sound(Group) VALUES(""Group A"")"
Debug.Print "Search String:"; sql_com

    'Now perform the command on the database
    Music_DB.Execute sql_com, dbFailOnError
Debug.Print "SQL command executed"

    'Close the database
    Music_DB.Close
Debug.Print "The database is closed....."

End Sub

2. Post Recovery file request problem

3. Re(4): "select having" problem

4. Job Opening

5. Re(2): "select having" problem

6. An optical allusion that will astound you, works on all spec pc's:) 6586

7. "select having" problem

8. Sort order ID problem on restore

9. "set nocount on" having no effect

10. Grouping and Having problem

11. GROUPING BY / HAVING clause problems...

12. Still Having problems with GROUP BY

13. Having problem with GROUP BY on alias