Select multiple rows with the same value(s)

Question

I have a table, sort of like this:

ID  |  Chromosome | Locus | Symbol | Dominance |
===============================================
1   |      10     |   2   |   A    |   Full    |
2   |      10     |   2   |   a    |   Rec.    |
3   |      10     |   3   |   B    |   Full    |
4   |      10     |   3   |   b    |   Rec.    |

I'd like to select all rows with the same locus and chromosome. For example, rows 3 and 4. There may be more than 2 at a time and they may not be in order.

I tried this:

SELECT *
FROM Genes
GROUP BY Locus
HAVING Locus='3' AND Chromosome='10'

But it always returns row 3, never row 4, even when repeated. I think I'm missing something obvious and simple, but I'm at a loss.

Can someone help?

Answer 1

You need to understand that when you include GROUP BY in your query you are telling SQL to combine rows. you will get one row per unique Locus value. The Having then filters those groups. Usually you specify an aggergate function in the select list like:

--show how many of each Locus there is
SELECT COUNT(*),Locus FROM Genes GROUP BY Locus

--only show the groups that have more than one row in them
SELECT COUNT(*),Locus FROM Genes GROUP BY Locus HAVING COUNT(*)>1

--to just display all the rows for your condition, don't use GROUP BY or HAVING
SELECT * FROM Genes WHERE Locus = '3' AND Chromosome = '10'