November 9, 2012 at 4:48 am
Hi, in my table runs is a col. am using sql server 2008.
i want to calculate how many 100's r there and how many 50's r there .
November 9, 2012 at 4:54 am
Can you post the DDL and Sample data as well as expected results as this will help us help you with a solution.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 9, 2012 at 4:58 am
Post DDL before CELKO come 😀
-----------------------------------------------------------------------------
संकेत कोकणे
November 9, 2012 at 5:07 am
Yes, provide sample data etc
Else:
SELECT
'number of 50s' as [Measure]
, COUNT(myCol) as [Value]
FROM myTable
WHERE myCol = 50
UNION
SELECT
'number of 100s' as [Measure]
, COUNT(myCol) as [Value]
FROM myTable
WHERE myCol = 100
HTH,
B
November 9, 2012 at 5:21 am
Assuming you have a table with columns such as PlayerName, Match, Score, then something like:
SELECT PlayerName,
SUM(CASE WHEN Score > 50 THEN 1 ELSE 0 END) AS [50s],
SUM(Case WHEN Score >= 100 THEN 1 ELSE 0 END) AS [100s]
FROM TableName
GROUP BY PlayerName
If you don't want the 100s to be included in the 50s, then you'll need to tweak the first CASE statement.
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply