July 24, 2015 at 3:15 am
Hi Sir,
I need Unique counts for one of my requirment.
DDL Script
CREATE TABLE test_data
(ID INT NULL,
NAME NVARCHAR(10) NULL,
YEAR INT NULL)
Insert Into Script,
INSERT INTO test_data VALUES (10,'S',2014)
INSERT INTO test_data VALUES (10,'C',2014)
INSERT INTO test_data VALUES (10,'P',2014)
INSERT INTO test_data VALUES (10,'S',2015)
INSERT INTO test_data VALUES (10,'C',2015)
INSERT INTO test_data VALUES (10,'P',2015)
INSERT INTO test_data VALUES (20,'S',2014)
INSERT INTO test_data VALUES (20,'C',2015)
INSERT INTO test_data VALUES (20,'P',2015)
INSERT INTO test_data VALUES (30,'S',2014)
INSERT INTO test_data VALUES (30,'P',2014)
Unique counts: this means that if a ID was both a C and a S, the ID would only be counted once.
Example below
For Year=2014 and for ID=10 the count would be 1
For Year=2015 and for ID=10 the count would be 1
For Year=2015 and for ID=20 the count would be 1
For Year=2014 and for ID=30 the count would be 1
So request you please how to this count?
July 24, 2015 at 3:25 am
Could you please expand the sample data to show when an ID will return more than 1 for a year?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2015 at 8:57 am
As Gail requested that you expand your sample data, she did so because we don't know exactly what you mean by the word unique. In this scenario, it could have many different meanings, so we need you to define EXACTLY what the meaning is. If you're just looking for an indicator that tells you that an ID value occurs in a given year, and that the NAME values are entirely irrelevant, that's a rather different situation than if the NAME values have an impact. If the NAME values are truly meaningless 100% of the time, then this would work:
DECLARE @test_data AS TABLE (
ID INT NULL,
NAME NVARCHAR(10) NULL,
[YEAR] INT NULL
);
INSERT INTO @test_data VALUES (10,'C',2014)
INSERT INTO @test_data VALUES (10,'P',2014)
INSERT INTO @test_data VALUES (10,'S',2015)
INSERT INTO @test_data VALUES (10,'C',2015)
INSERT INTO @test_data VALUES (10,'P',2015)
INSERT INTO @test_data VALUES (20,'S',2014)
INSERT INTO @test_data VALUES (20,'C',2015)
INSERT INTO @test_data VALUES (20,'P',2015)
INSERT INTO @test_data VALUES (30,'S',2014)
INSERT INTO @test_data VALUES (30,'P',2014);
SELECT [YEAR], ID, COUNT(DISTINCT ID) AS ID_COUNT
FROM @test_data
GROUP BY [YEAR], ID
ORDER BY [YEAR], ID
I found it odd that the results of this query identify ID 20 in 2014, but your example does not. It's little things like this that require that specifications be exacting and detailed, leaving absolutely NOTHING in the way of a "stone un-turned", so to speak. Sample data needs to cover ALL reasonably possible scenarios, however unlikely they may be, as a query is NOT a human being, and can't make a different choice on what to do under different circumstances without being told in advance (in the query), what to do in that situation. Let us know what you mean, exactly, by unique, and it will be a lot easier for us to help you.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 24, 2015 at 5:32 pm
Even this would work.SELECT DISTINCT [YEAR], ID, 1 AS [ID_COUNT]
FROM @test_data
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply