April 11, 2013 at 3:43 am
hi guy i need help with a query!!!
basically i have a table with l01 and 109
table looks a little like this
L01 a09
123 1
123 2
123 z
234 z
345 1
345 z
456 2
basically i want a count of l01 where a09 is only ever z
so in this instance only 234 would be counted for as 123 has 1, 2 and z and 345 has 1 and z and 456 has 2.
please help
April 11, 2013 at 3:58 am
-- create some sample data to test against
DROP TABLE #Sample
CREATE TABLE #Sample (L01 INT, a09 CHAR(1))
INSERT INTO #Sample (L01, a09)
SELECT 123, '1' UNION ALL
SELECT 123, '2' UNION ALL
SELECT 123, 'z' UNION ALL
SELECT 234, 'z' UNION ALL
SELECT 345, '1' UNION ALL
SELECT 345, 'z' UNION ALL
SELECT 456, '2'
-- solution
SELECT COUNT(L01)
FROM #Sample s
WHERE NOT EXISTS (
SELECT 1
FROM #Sample si
WHERE si.L01 = s.L01
AND si.a09 <> 'z')
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 11, 2013 at 4:01 am
hi thanks for your efforts but i cannot drop the tables is there a way of doing it without altering any of the tables
April 11, 2013 at 4:05 am
mamzy.rahman (4/11/2013)
hi thanks for your efforts but i cannot drop the tables is there a way of doing it without altering any of the tables
-- create some sample data to test against
You didn't provide a sample data script (see the link in my sig) so I made some up using the figures in your post.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 11, 2013 at 4:07 am
i cant create just query 🙁
April 11, 2013 at 4:08 am
mamzy.rahman (4/11/2013)
i cant create just query 🙁
What is the name of the table you are using?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 11, 2013 at 4:10 am
aims
April 11, 2013 at 4:14 am
In the little query I wrote above, change #Sample to aims.
Just this query, not the sample data generator:
-- solution
SELECT COUNT(L01)
FROM #Sample s
WHERE NOT EXISTS (
SELECT 1
FROM #Sample si
WHERE si.L01 = s.L01
AND si.a09 <> 'z')
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 11, 2013 at 4:17 am
problem is tho were i have put 1 2 3 and z there jus examples the actual data is completely different and would be impossible for me to do the union side for all the diffferent data as there millions of them :S
April 11, 2013 at 4:24 am
mamzy.rahman (4/11/2013)
problem is tho were i have put 1 2 3 and z there jus examples the actual data is completely different and would be impossible for me to do the union side for all the diffferent data as there millions of them :S
... the union side ...
That's not part of the solution - it's part of the sample data generator which I used to test a potential solution against.
No problem. First, read this: http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
It will demonstrate to you how to post sample data. When you've read it, post up a script to generate some sample data so folks here can test against your actual table and column names and data which is realistic rather than 'z'.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply