April 5, 2017 at 5:53 pm
Hello,
I hope someone can help me out. I have a table and in one of the columns is the various version of SQL we have, so the column consists of SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 R2 etc. and in another column is the environments they are spread over, I am trying to count how many of each are in each environment to use in a report.
I have tried using count(SQL Version) over(partition by environment) as [Number of SQL Versions per Environment), but the number is the same for the hole column, it does not change when the row is clearly another environment. I was expecting some like...
Prod SQL server 2008 64
Prod SQL server 2008 64
Test SQL server 2008 38
Dev SQL server 2008 24
Dev SQL server 2012 42
You get the idea. Should I be doing something other than using over and partition by? Ideally, it would be rounded up to unique rows.Thank you for any help.
Regards,
D.
April 5, 2017 at 6:08 pm
CREATE TABLE DBs(Environ VARCHAR(4), SQLVersion VARCHAR(14));
SELECT Environ, SQLVersion, COUNT(*) AS Count
FROM DBs
GROUP BY Environ, SQLVersion;
April 5, 2017 at 7:03 pm
If the integer column is the number of environments and you want the total of it, then you're looking for the SUM function.
The CTE is to put the data in a consumable format. The query of can be used directly against your table by substituting your table and column names.
WITH cteTable AS (
SELECT Env, Version, Cnt
FROM (VALUES('Prod', 'SQL server 2008', 64),
('Prod', 'SQL server 2008', 64),
('Test', 'SQL server 2008', 38),
('Dev', 'SQL server 2008', 24),
('Dev', 'SQL server 2012', 42)
) x (Env, Version, Cnt)
)
SELECT Env, Version, Total = SUM(Cnt)
FROM cteTable
GROUP BY Env, Version;
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply