How do I used Count(*) with DAL2?

Question

I want to get counts for various groupings of data in some of my tables and am not sure if it is possible using DAL2.

I want perform queries such as:

SELECT  DISTINCT productType, COUNT(*) FROM Products GROUP BY productType

The information I come across only includes examples that allow the user to specify the WHERE part of the SQL. This example unfortunately skirts right around the WHERE part of the query so I am not sure how I should approach this using DAL2. Is it possible using DAL2 or do I need to query the database another way? If it can be done using DAL2, how do I execute such a query?

Answer 1

The examples showing only the WHERE part mean that PetaPoco fills in the "SELECT * FROM TableName" part for you, but of course you can execute your own sql statement

In your case:

public class ProductCount {
   public int ProductType {get; set;}
   public int Count {get; set;}
}

var ProductCountList = db.Fetch<ProductCount>(@"SELECT DISTINCT productType, 
        COUNT(*) as Count 
        FROM Products 
        GROUP BY productType");