ansaurus

Question

SQL query to get all the combinations of two columns

Answer 1

+7 A:

SELECT Flag1, Flag2, COUNT(GeneId) as NumGenes
FROM genetable
GROUP BY Flag1, Flag2

Amadan 2010-07-10 15:32:08

How does this cater for the requirement for flag _combination_ counts? For example - how does this tell you the count of all rows that have Flag1 = 1 _and_ Flag2 = 1?

Oded 2010-07-10 15:36:41

`GROUP BY Flag1, Flag2` means split the rowset into sections where `(Flag1, Flag2)` are identical. Thus, `(17, 0, 1)` and `(19, 0, 1)` would get grouped together, since they are both `(0, 1)` in the flags department. Then `COUNT(GeneId)` counts all rows where `GeneId` is not `NULL`. Other possibilities are `COUNT(*)` (include `NULL` rows) and `COUNT(DISTINCT GeneId)` which would count repeats only once.

Amadan 2010-07-10 15:49:16

@Oded, i don't understand the issue with that query, it will return 4 rows, one for each combination. Provided that each combination exists in the table, i.e if there were no rows in the table then there would be no rows returned from the query.

Chris Diver 2010-07-10 15:53:47

@Chris Diver - thanks. Too hot here today, brain slightly fried...

Oded 2010-07-10 16:06:43

I know that feeling :)

Chris Diver 2010-07-10 16:08:44

Thank You Amadan :)

Sashikiran Challa 2010-07-10 16:56:39

ansaurus

tags:

views:

answers:

SQL query to get all the combinations of two columns

related questions