sql restricted count query

Question

I'm not certain how to structure a SQL query to return the number of rows where a column equals certain values.

For instance,

In table myTable, how can I return the count of all the rows where myColumn = "xyz" and where myColumn = "abc"? Is this possible with a single query?

To clarify, say there are 10 rows where myColumn = "xyx" and 7 rows where myColumn = "abc", the query would return something like:

firstCountResult: 10
secondCountResult: 7

Answer 1

How about?:

SELECT 
    COUNT(*),
    myColumn
FROM myTable
WHERE myColumn IN ('xyz', 'abc')
GROUP BY myColumn

This approach also works with other aggregate functions like MIN, MAX, AVG, SUM... You get the aggregate result per the grouped column's value versus across all rows.

--min myDate per myColumn value
SELECT
    MIN(myDate),
    myColumn
FROM myTable
WHERE myColumn IN ('xyz', 'abc')
GROUP BY myColumn

--sum of myNumericCol per myColumn value
SELECT
    SUM(myNumericCol),
    myColumn
FROM myTable
WHERE myColumn IN ('xyz', 'abc')
GROUP BY myColumn

Answer 2

You want to group the count by mycolumn so you do this

select 
         myColumn, count(*)
from  
         myTable 
where 
         myColumn in ('xyz','abc')
group by 
         myColumn