Display value, count of one field based on value of another field
Let's say that I have a CSV of individual product sales where one field is the product name (e.g., "900X") and another is the product type (e.g., "Retail," "Commercial"). I'm trying to show a table where the first column lists the product and the second is the count of the rows for that product provided it's only for type=retail; e.g., I want to do
select name, count(1) from products where type = 'Retail'
For the first column, I want to only include names when the type is "Retail." I tried
but it's not valid. I'm not sure how to say that I just want the value without any wrapping function call.
For the second column, the following appears to be syntactically correct: