Counting Rows
-
SELECT
CASE
WHEN (IsNull(customer.current_version,' ') = ' ') THEN 0
WHEN SUBSTRING(customer.current_version,1,1) = 'V' THEN SUBSTRING(customer.current_version,2,1)
ELSE SUBSTRING(customer.current_version,1,1)
END AS Version,
COUNT(customer.current_version) AS NumberOfUsers
FROM customer
WHERE (customer.platform = 'W' OR customer.platform = 'B') AND customer.supp_cycle <> 'X'
GROUP BY customer.current_version
ORDER BY Version
The data is composed of entries like 7.01a, 7.02b, 6.55, 5.43 etc.
I want to count how many 7's, 6's 5's etc.
However the above code only groups them by 7.01 value instead of 7 value or as I have it above it says Version is undefined.
I am stuck.. any help would be appreciated.
Thanx
-
Derived table (named dtMajorVersion) to get your data filtered, and the major version number extracted as a numeric, then a simple Count/Group By on the derived table:
Select Version, Count(*)
From
(
Select
Cast(
Case
When customer.current_version Is Null Then '0'
When Left(customer.current_version, 1) = 'V' Then Substring(customer.current_version,2,1)
Else Substring(customer.current_version,1,1)
End
As smallint) As Version
From Customer
Where (customer.platform = 'W' OR customer.platform = 'B') AND customer.supp_cycle <> 'X'
) dtMajorVersion
Group By Version
Order By Version
-
Thank you very much.. it works well.
There is only one thing I do not understand. What is the command 'dtMajorVersion' mean?
-
It doesn't mean anything, it's just a name/alias applied to the derived table. By convention, I prefixed it with "dt", and called it "MajorVersion" because it extracts the major version number from the data. You could call it FredFlintstone if you wanted.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply