Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 27th, 2006, 06:32 PM
Registered User
 
Join Date: Jan 2006
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Simple Query help pls?

Hi all. I'm very new to SQL and have almost no background in DBs or dev of any kind.

That said Im trying to setup a website for my rec league softball team and have setup 3 dbs (I know I could have used 1 DB w/3 tabls but Im not going back now LOL). They are Players. Games and Stats_Entry and each has 1 table called Results. The one I want to query is stats_entry and it has the basic baseball columns. Player (from player DB) then Date (from Game DB) then Hits, ABs, Avg, Sgl, Dbl, Tpl, HR, Walks, Sac, FC. I have some basic queries setup to get team stats and can filter by Player or Datebut I need a few queries that are far beyond my meager skills.

1. I need to be able to get player averages by game and totals by palyer. I can get Team total average by using the sum(hits)/sum(abs) but I dont know the proper way to do it all in a single query.

3. I add the numereric(3,3) to the queries to show average as .380 instead of 0.38033434334. I tried about a million different combos without success.

3. I'd like to use the toprow query to get the leader of each stat category by Player so I can see a list of Players with highest avg, hits etc.

I have a few more but if someone can help me with these Ill go farther.

I use an Access DB on a IIS 5.x and Frontpage 02.

My site is located at http://bikecenter.servebeer.com and go to login (No user or pw while testing).

Thank you very much.

Mitch




 
Old January 28th, 2006, 03:54 PM
Registered User
 
Join Date: Jan 2006
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well upon further (guessing) I was able to get team totals with this SQL statement

SELECT Player, sum(ABs)
FROM Results
GROUP BY Player


I can then remove the Player field in all columns and get the desired data output.

What I havent figured out is where I would insert the NUMERIC(4,3) or DEC(4,3) DECIMAL(4,3) statements (not positive which command actually works in an Access DB though) in the SQL above so that the Decimal place in AVG doesnt show up randomly (ex: 0.389990989) instead of the desired .390.

Any help on sorting database results by column would be greatly appreciated as well. Ive tried a few suggestions but they werent detailed enough for me to get it working.

Thanks again.
Mitch

 
Old February 1st, 2006, 10:56 PM
Registered User
 
Join Date: Jan 2006
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Is what Im asking difficult or incorrectly asked or not described correctly? Seems like a simple question to me but I guess Im wrong.

Maybe I picked the wrong forum?>

Oh well, buh bye and thanks for all the suggestions and responses...

Mitch

 
Old February 1st, 2006, 11:57 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 596
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Hi Mitch,
No there is nothing difficult about what you want to do from what I have read, Im not an SQL guru but have many years experience with it and the first point of advise I would give you is to put all the tables in one database, that is the very purpose of a database. to try to do what you are saying in three databases is ridiculas, especially for a person new to SQL because you have to join databases as well as tables.
There are many alternatives to putting in one DB but all would be the wrong thing to do in such a situation.
 
Quote:
quote:I know I could have used 1 DB w/3 tabls but Im not going back now LOL

TRUST ME, go back now.

If this site is going to last you any period of time and you continue to use it you will face lots of problem every time you try to enhance you app.

If you are prepared to do this you will find that you will get a lot of help from this forum. But please be aware that this is an SQL forum and details such as joining databases in Access may be too product specific to get any real answer, hence put you tables in one database and we can help.

If any gurus dissagree with me, please comment and help Mitch out.

Rod





======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================
 
Old February 1st, 2006, 11:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 596
Thanks: 1
Thanked 3 Times in 3 Posts
Default

PS, your site looks good.

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================
 
Old February 3rd, 2006, 05:13 PM
Registered User
 
Join Date: Jan 2006
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you very much for the reply! Ill consider creating YADB with 3 tables but at this point Im not really using the Player or Game DB just the Stats DB.

At this point Im pretty much done except Im still unable to get the proper (baseball) decimal position (ex: .389). I can find the syntax (NUMERIC(4,3) dec(4,3 and DECIMAL(4,3)) but cant find where in the sql query it belongs. Here is an example.

SELECT sum(Hits)/(sum(ABs)-sum(Walks))
FROM Results

The above example gives me batting average but it seems no matter where I put the any of the above Numeric,DEC,DECIMAL statements at any position in the query it hoses the query.

Can anyone please tell me how to set my decimal position to a consistent 3,3 or 4,3 precision and scale? Im thinking 4,3 only because occasionally someone will be batting a perfect 1.000.

Anyone?






Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel vba query not working, pls help me. kavisundar SQL Language 0 May 16th, 2007 10:14 AM
Mysql query...Pls help me raaj MySQL 2 March 17th, 2007 12:43 AM
simple but knowledgeable pls help balaji23_d SQL Server 2000 4 March 12th, 2007 02:21 AM
pls give the query for XML data in Sqlserver2005 veeruu SQL Server 2005 0 July 27th, 2006 05:07 AM
Query Help pls denver Access VBA 7 June 5th, 2006 12:29 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.