-5

I have data in a table "customer" like

ID    NAME    CITY
11    John     A
12    Peter    B
13    Robin    A
14    Steve    C
15    Methew   D
16    Matt     C
17    Nancy    C
18    Oliver   D

I want the query that only shows the data for every 2 customers that are in the same city.

Output should be,


ID    NAME    CITY
11    John     A
13    Robin    A
15    Methew   D
18    Oliver   D
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Kashan N
  • 7
  • 4
  • You want to perform the query in SQL? – jishan siddique Nov 28 '19 at 04:12
  • You want only `A`, `D` city record correct? – jishan siddique Nov 28 '19 at 04:14
  • 1
    SO isn't a code writing service. We're more than happy to help once you've made an effort to solve the problem yourself. Once you've done so and run into difficulties, you can explain the problem you're having, include the relevant portions of your code, and ask a specific question related to that code and we'll try to help. You'll find your experiences here will be much better if you spend some time taking the [tour] and reading the [help] pages to learn how the site works and what our expectations are before you begin posting. – Ken White Nov 28 '19 at 04:24

2 Answers2

3

The following query does this

select a.ID1,a.Name1,a.City,b.cnt_of_customers
from Customers as a
        ,(   SELECT City ,count(*) as cnt_of_customers
              FROM Customers
           GROUP BY  City 
          HAVING count(*)=2) as b
where a.city=b.city

enter image description here

George Joseph
  • 5,842
  • 10
  • 24
  • I didn't down vote but the most likely reason is that you answered a question where the OP didn't even show *any* effort at all to solve it on his own. • According to [ask] he should at least show what he has tried (and didn't work), or where he got stuck and errors, or at least what he has researched so far. • Some people consider this as a bad habit and downvote these answers. See some big discussions about that on Meta: [For example this one](https://meta.stackoverflow.com/questions/255459/is-it-okay-to-downvote-answers-to-bad-questions). – Pᴇʜ Nov 28 '19 at 07:18
  • So personally I think down-votes without leaving a comment don't help the author of the answer to improve (and therefore are pretty useless). Nevertheless don't worry about a view down-votes (even if it is frustrating) people are free to vote whatever they think, and down-votes on correct answers happen to everyone. • To avoid situations like these focus on answering good questions only. Personally I only answer bad questions, if I think that the answer could be useful for furture/other readers, so there is a bigger value in the answer that helps other people too. – Pᴇʜ Nov 28 '19 at 07:25
  • Thanks for the feedback and negating the downvote, I went ahead creating ms-access file with the columns and tables being used by the OP, even though he should have attempted to provide the DDL, and also what he tried. I was being nice to him since he is relatively new (OP member since last 2 months). – George Joseph Nov 28 '19 at 07:51
  • Thanks @Pᴇʜ appreciate the feedback – George Joseph Nov 28 '19 at 09:58
0

try this

select * from Customer where City IN (
 select  city  from Customer as c
  group by city having Count(City) = 2)
Santosh
  • 12,175
  • 4
  • 41
  • 72