Drupal - Count results in Views using aggregation

Yes, it is possible in Views 3 out of the box.
The idea is the same as in SQL aggregations. Lets see an example:

1. Edit your view and enable Views aggregations:
   

2. Remove default sort criteria.

3. Add fields “Content: Type” and “Content: Nid”:
   Select COUNT function for Content: Nid:

To see what happens just turn on checkbox “Show the SQL query” at global Views settings page.

SELECT node.type AS node_type, COUNT(node.nid) AS nid
FROM 
{node} node
WHERE (( (node.status = '1') ))
GROUP BY node_type
LIMIT 10 OFFSET 0

So, we are grouping nodes by node_type and calculating count for this groups.

Great answers here though your original question does not state what you are going to do with the count value. Presumably you want to display it?

If so, here's another solution:

Assuming that your View is already set up to filter by items of content type, then you can also add a header in the View and choose the "Global: Result summary" option and use the token variables provided in the text area.