Select group by with both total and count where

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 2005 Forums

Transact-SQL (2005)

Select group by with both total and count where

Author

Topic

mstoeber
Starting Member

1 Post

Posted - 2011-01-02 : 20:23:11

Both of these statements work, but is there someway to combine them. Statement 1 is the count of plays - statement 2 is a count of plays where a field length is less than 10 characters long.

-- count of plays
select cast([gamedate]as varchar(6))+' '+homeclubcode+' vs '+visitorclubcode, count(*) from videodirectorreport where gamedate >'07/01/2010' group by gamedate,homeclubcode, visitorclubcode order by gamedate

-- number of plays without participation information
select cast([gamedate]as varchar(6))+' '+homeclubcode+' vs '+visitorclubcode, count(*) from videodirectorreport where len(homeplayers)<'10' and gamedate >'07/01/2010' group by gamedate,homeclubcode, visitorclubcode order by gamedate

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-01-02 : 21:16:25

You can use CASE like this:


SELECT CAST(gamedate AS VARCHAR(6)) + ' ' + homeclubcode + ' vs ' + visitorclubcode,
       COUNT(*) AS count_of_plays,
       COUNT(CASE WHEN LEN(homeplayers) < '10'
                       THEN 1
                  ELSE NULL
             END) AS number_of_plays_without_participation_information
  FROM videodirectorreport
 WHERE gamedate >'07/01/2010'
 GROUP BY gamedate, homeclubcode, visitorclubcode
ORDER BY gamedate;

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-03 : 04:44:00

Alsways use unambigious date format
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources