Using the Count function - 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

Transact-SQL (2000)

Using the Count function

Author

Topic

puush
Starting Member

14 Posts

Posted - 2009-04-20 : 19:21:44

Can you please help me with this script? I have attached a screen capture of the script and resulting query in Query Analyzer and I have also included the script below.

I’m basically querying our database to find out what machines our top 10 players play the most in a given date range along with their Coin In and Points Earned for that same given date range.

“I want the query to count how many times and how long a player plays a slot in a given date range and display that on "one" row along with Sum of CoinIn and Points Earned during that same given date range.”

The script I wrote actually querys this data but as you can see in the query results, it shows the same slot a customer played on its own row. So we end up with 1300 rows. Basically this script will help us see what machines our high players liked the most during each month. I am weak using the “count” function in SQL. Can you please help us with this script Michael? Thanks

----------------------------------------------------------------------------------------------------------------------
select meta_id, slotnumber, gamingdate, timeplayed, starttime, endtime, cashin, ptsearned
from cds_statdetail
where meta_id in ('1146', '1017', '1954', '1103',
'4659', '452', '1066', '2565', '882', '1189')

and gamingdate between '03/01/2009' and '03/31/2009'

order by meta_id, timeplayed desc
----------------------------------------------------------------------------------------------------------------------

Part of the query results:

meta_id slotnumber gamingdate timeplayed starttime endtime cashin ptsearned
452 1103 3/26/2009 3600 3/26/2009 3/26/2009 $2,080.00 2080
452 1103 3/27/2009 3600 3/27/2009 3/27/2009 $2,296.00 2296
452 1103 3/26/2009 3600 3/26/2009 3/26/2009 $2,055.80 2055
452 1103 3/28/2009 3600 3/28/2009 3/28/2009 $2,418.00 2418
452 1103 3/28/2009 3600 3/28/2009 3/28/2009 $2,276.00 2276
452 1103 3/31/2009 3600 3/31/2009 3/31/2009 $2,316.01 2316
452 1103 3/20/2009 3600 3/20/2009 3/21/2009 $2,048.00 2048
452 1103 3/20/2009 3600 3/20/2009 3/20/2009 $1,954.00 1954
452 1103 3/20/2009 3600 3/20/2009 3/20/2009 $1,956.00 1956
452 1103 3/21/2009 3600 3/21/2009 3/21/2009 $2,072.00 2072
452 1103 3/21/2009 3600 3/21/2009 3/21/2009 $2,230.00 2230
452 1103 3/20/2009 3517 3/21/2009 3/21/2009 $2,196.00 2196
452 1103 3/26/2009 3407 3/27/2009 3/27/2009 $1,894.00 1894
452 1103 3/27/2009 2737 3/27/2009 3/27/2009 $2,000.00 2000
452 1103 3/20/2009 2653 3/20/2009 3/20/2009 $1,190.00 1190
452 1103 3/26/2009 2423 3/26/2009 3/27/2009 $1,484.00 1484
452 1103 3/21/2009 2351 3/21/2009 3/21/2009 $1,838.00 1838
452 1102 3/20/2009 2147 3/21/2009 3/21/2009 $1,428.00 1428
452 1222 3/20/2009 2086 3/20/2009 3/20/2009 $354.90 355
452 5543 3/27/2009 1963 3/27/2009 3/27/2009 $1,439.00 1439
452 1103 3/7/2009 1849 3/7/2009 3/7/2009 $660.00 660
452 1103 3/27/2009 1738 3/27/2009 3/27/2009 $1,412.00 1412
452 1103 3/26/2009 1720 3/26/2009 3/26/2009 $1,228.00 1229
452 5538 3/26/2009 1629 3/26/2009 3/26/2009 $854.00 854

______________________________
Michael, IT Director Dude
From the Great land of Lake Co, CA

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-20 : 19:43:04

So you want 1 row per meta_id and slotnumber? See if this works:


select	meta_id
	,slotnumber
	,count(*) gamecount
	,sum(timeplayed) total_timeplayed
	,sum(cashin) total_cashin
	,sum(ptsearned) total_ptsearned
from cds_statdetail
where meta_id in ('1146', '1017', '1954', '1103',
'4659', '452', '1066', '2565', '882', '1189')

and gamingdate between '03/01/2009' and '03/31/2009'
group by meta_id
	,slotnumber
order by meta_id, sum(timeplayed) desc

Be One with the Optimizer
TG

puush
Starting Member

14 Posts

Posted - 2009-04-20 : 21:08:33

awesome man. It works. You guys are great. What does the "count (*)" mean.

______________________________
Michael, IT Director Dude
From the Great land of Lake Co, CA

dsindo
Starting Member

45 Posts

Posted - 2009-04-21 : 16:30:14

count (*) count (*) means all rows returned

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources