query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

query question

Author

Topic

John T.
Posting Yak Master

112 Posts

Posted - 2003-05-02 : 16:42:22

First crack at doing a query like this. I have two tables that are involved. First table is a usersTable. Second is a totalsTable. It has a ue field which is DECIMAL(6,2), result field which is varchar.
I want to find the total of the ue field. Using the result field(either a win or loss), I want to obtain the total of wins and total of losses. Along with the percentage of wins. I will be binding this for display to a data grid.
Something tells me this isn't it.
CREATE PROCEDURE Testing As
Declare @earned DECIMAL(6,2), Declare @wins SMALLINT, Declare @losses SMALLINT, Declare @pct DECIMAL(5,2),@cname VarChar(50)
SELECT @cname = CName FROM usersTable
SELECT @earned = SUM(ue) FROM totalsTable
SELECT @wins = COUNT(result)FROM totalsTable WHERE result = 'win'
SELECT @losses = COUNT(result) FROM totalsTable WHERE result = 'loss'
SELECT @pct = @wins / (@wins + @losses)

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-02 : 16:51:13

This assigns only 1 row to a variable from a table which may contain many rows.

SELECT @cname = CName FROM usersTable

Are you planning on looping through recordsets.
This procedure would return 5 recordsets.

SELECT
SUM(CASE WHEN result = 'win' THEN 1 Else 0) As Wins,
SUM(CASE WHEN result = 'loss' THEN 1 ELSE 0) As Losses
FROM totalsTable

If you want by user then show us the ddl for the 2 tables and give an example but it would basically be the above example with an additional join and a group by clause.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-02 : 16:56:58


select Cname,
       sum(Ue) as earned,
       sum(case when result = 'win' then 1 else 0 end) as wins,
       sum(case when result = 'loss' then 1 else 0 end) as losses,
       1.0 * sum(case when result = 'win' then 1 else 0 end) / count(*) as Pct
FROM
    TotalsTable
INNER JOIN
    UsersTable
ON
    TablesTable.<someID> = UsersTable.<someID>
GROUP BY CName

I hope there is a relationhsip between the users table and the totals table.

If not, then there needs to be. If so, indicate with fields in the Join expression.

Try to get a feel for when to set variables (rarely!) with when to return DATA from tables. Look at the results of running this SELECT statement and try to get a feel for the power of using joins, SELECT's, WHERE clauses, the CASE statements, etc. Read up on GROUP BY and aggregate functions, look at the examples in books on-line.

Good luck! Experiment -- not with stored procedures, but rather with simple SELECT statements and all kinds of groupings, CASE statements, etc.

start by returning 1 field, 1 formula, 1 grouping. add more. add a CASE. add a SUM(). see what you can do!

- Jeff

Edited by - jsmith8858 on 05/02/2003 16:58:05

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-02 : 16:58:11

quote:

Experiment -- but not with stored procedures, with simple SELECT statements and all kinds of groupings, CASE statements, etc.

I completely agree with this. Start out with SELECT statements, once you have the results that you want, then build it into a stored procedure. It is much easier to debug SELECT statements than a stored procedure.

Tara

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-02 : 19:31:13

T,
Have you used the QA debugger on an sp.
It's pretty nice. Still I agree in the modular development and testing concept.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-02 : 19:52:46

Yes I was referring to modular stuff. I have only used the debugger a few times though.

Tara

John T.
Posting Yak Master

112 Posts

Posted - 2003-05-03 : 08:57:12

Thanks so very much for the help. That books on line section is very good.
I always work on finding what I need myself before I come here and make a post. The above code that Jeff wrote works nicely. I added the following :
1.0 * sum(case when result = 'win' then 1 else 0 end) / count(*) * 100 as Pct
This gives me 33.333333 percent rather than .33333333333. I did look for something though that I can't find. How to express this as 33.3 percent. I tried the Round function that I found in books on line:
Round(1.0 * sum(case when result = 'win' then 1 else 0 end) / count(*),1) as Pct
I also tried various ways of using DECIMAL in the expression.
pct(4,1),pct DECIMAL(4,1)
And others.
Has to be something small. Not easy to find. Did search with "sum/case""decimal" and couldn't find what I am looking for.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-03 : 09:06:52

This should do it:

SELECT Str(1.0 * sum(case when result = 'win' then 1 else 0 end) / count(*) * 100, 5, 2) + '%' as Pct

John T.
Posting Yak Master

112 Posts

Posted - 2003-05-03 : 09:18:48

Thanks Rob. I saw that % sign in books on line but wasn't sure it was what I was after. Once again, thanks. Never would have thought of using the string function thing.

John T.
Posting Yak Master

112 Posts

Posted - 2003-05-04 : 10:22:35

Can somebody tell me if I am on the right track here. I am really just experimenting with code. I am trying to take the "sum(Ue) as earned" statement and have the display have a plus sign if positive.
I think I can do it with variables but really would like to see how it is done without.
Thanks.
<code>
select Cname,
sum(Ue) as earned,
sum(case when result = 'win' then 1 else 0 end) as wins,
sum(case when result = 'loss' then 1 else 0 end) as losses,
Str(1.0 * sum(case when result = 'win' then 1 else 0 end) / count(*)* 100,5,2) + '%' as Pct
FROM
TotalsTable
INNER JOIN
UsersTable
ON
TablesTable.<someID> = UsersTable.<someID>
GROUP BY CName

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-04 : 11:02:32

select Cname,
CASE SIGN(SUM(Ue))
WHEN 1 THEN '+' + SUM(Ue)
WHEN -1 THEN '-' + SUM(Ue)
ELSE 0 as earned,
sum(case when result = 'win' then 1 else 0 end) as wins,
sum(case when result = 'loss' then 1 else 0 end) as losses,
Str(1.0 * sum(case when result = 'win' then 1 else 0 end) / count(*)* 100,5,2) + '%' as Pct
FROM
TotalsTable
INNER JOIN
UsersTable
ON
TablesTable.<someID> = UsersTable.<someID>
GROUP BY CName

Edited by - ValterBorges on 05/04/2003 11:17:44

John T.
Posting Yak Master

112 Posts

Posted - 2003-05-04 : 11:11:54

Thanks Valter. I did a search on the SIGN function. See it returns 1 if pos, -1 if neg, and 0 if 0. You guys know your beans and pototoes.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-04 : 11:15:46

Actually you can even simplify and take out the str function.
The casting is implicit.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources