0

I have a table in SQL Operations Studio (SQL Server) with columns A, B, C, D. I want to query all rows with the following conditions:

EDITED WITH SAMPLE DATA

   A    B    C    D
1  a    b    c    r
2  g    b    c    r
3  n    h    f    r
4  k    u    e    z
5  h    i    e    z
  1. Values in Column C & D match at least 2 times
  2. Value in Column D must appear at least 3 times

Return:

   A    B    C    D
1  a    b    c    r
2  g    b    c    r
3  n    h    f    r
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
thepez87
  • 221
  • 2
  • 12
  • 1
    sample data with expected result will be great – Squirrel Oct 18 '18 at 08:04
  • 1
    Sample data is best served as [DDL](https://en.wikipedia.org/wiki/Data_definition_language) + [DML](https://en.wikipedia.org/wiki/Data_manipulation_language). Please [edit] your question to include it, your current attempt and your desired results. For more details, [read this.](https://dba.meta.stackexchange.com/questions/2976/help-me-write-this-query-in-sql) – Zohar Peled Oct 18 '18 at 08:04
  • You could cut down the sample data to 2 and 3 – P.Salmon Oct 18 '18 at 08:04
  • The 3 row in your expected output does not seem to match the description - the `f` value only exists once in column `c` – Zohar Peled Oct 18 '18 at 08:32

1 Answers1

1

Here is one simple way to do it is to use a cte with a couple of count...over expressions.

First, create and populate sample table (Please save us this step in your future questions)

DECLARE @T AS TABLE
(
    A char(1), 
    B char(1), 
    C char(1), 
    D char(1)
);    

INSERT INTO @T (A, B, C, D) VALUES
('a', 'b', 'c', 'r'),
('g', 'b', 'c', 'r'),
('n', 'h', 'f', 'r'),
('k', 'u', 'e', 'z'),
('h', 'i', 'e', 'z');

Then, the cte:

WITH cte AS
(
    SELECT A, B, C, D,
            COUNT(*) OVER(PARTITION BY C, D) As CDCount,
            COUNT(*) OVER(PARTITION BY D) As DCount
    FROM @T
)

The query:

SELECT A, B, C, D
FROM cte
WHERE CDCount >= 2     
AND DCount >= 3
ORDER BY A

Results:

A   B   C   D
a   b   c   r
g   b   c   r
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • Ok, now that you have sample data it's clear that my first version was, in fact, correct, so I've rolled back my previous edit. – Zohar Peled Oct 18 '18 at 08:27
  • Analytic functions really are useful for this question. The aggregation approach would take 2 subqueries (I think); not so graceful. – Tim Biegeleisen Oct 18 '18 at 08:32
  • @TimBiegeleisen yes, I though about going with a group by but as I started to write it I suddenly realized that using count() over would simplify the script a lot – Zohar Peled Oct 18 '18 at 08:34
  • @ZoharPeled this is not running for me. I'm getting a syntax error at the first COUNT that doesn't make sense: "incorrect syntax near '(', expecting ')', EXCEPT or UNION" – thepez87 Oct 18 '18 at 09:31
  • Perhaps you've missed a comma or something - I can't know what's wrong with your code - [my code is working just fine.](https://rextester.com/SLDSN99797). If you could post the code you are actually using, perhaps I'll be able to help you fix it. – Zohar Peled Oct 18 '18 at 09:38