Conditional Count(*)

In a group-by statement, how can i get the count(*) of a given column conditioning this column independently of the whole group-by statement?

I have this query:

SELECT field1, SUM(field2) WHERE blah blah FROM table GROUP BY field1

It's all good.

I need to add a column with the count for a specific value on a column:
COUNT(field3) where field3 = 1

How can I insert this second query on the first one?

Thanks a lot !!!

CarlosUse SUM and DECODE:
SELECT field1, SUM(field2), SUM(DECODE(field3,1,1,0))
WHERE blah blah FROM table GROUP BY field1

;)|||I'd use SQL to achieve the same purpose. Something like:SELECT col1, Sum(col2), Sum(CASE WHEN 1 = col3 THEN 1 END)
FROM myTable
WHERE blah
GROUP BY col1Bear in mind that the WHERE clause gets first shot at the data, so you'll only see rows that pass the WHERE criteria. If you need to count all of the rows with col3 = 1 instead of just the rows that meet your criteria, then I'd use a "nested query", something like:SELECT col1, Sum(col2), (SELECT Count(*) FROM myTable AS z WHERE 1 = z.col3)
FROM myTable
WHERE blah
GROUP BY col1-PatP|||Thanks a lot !!!

It works great !!..

Carlos|||I have a similar question...

I have a db with helpdesk ticket info. I want to get the number of created, open, and closed tickets by each site (and the % closed if possible). Here is what I want the output to look like.

Created Open Closed %Closed
Site1 #created site1 #open site1 #closed site1 #closed/#created
Site2 #created site2 #open site2 #closed site2 #closed/#created
Site3 #created site3 #open site3 #closed site3 #closed/#created
Site4 #created site4 #open site4 #closed site4 #closed/#created

The site name is in a field called assigned_to_worker_location, and the status is in a field called status_lookup_value. When I used the code below, it does total by site, it just give me everything for all columns and rows. Here is the code:
==============================================
SELECT workitem.assigned_to_worker_location as Site,
(SELECT COUNT(*)
FROM dbo.HD_workitem_current_view as wi
WHERE (datepart(mm,wi.workitem_created_on)=datepart(mm,g etdate()))) as "Created",
(SELECT COUNT(*)
FROM dbo.HD_workitem_current_view as wi
WHERE wi.workitem_status_lookup_value = 'Open' or
wi.workitem_status_lookup_value = 'Wait-User' or
wi.workitem_status_lookup_value = 'Wait-Vendor' or
wi.workitem_status_lookup_value = 'Wait-Part' or
wi.workitem_status_lookup_value = 'Requested') as "Open",
(SELECT COUNT(*)
FROM dbo.HD_workitem_current_view as wi
WHERE (wi.workitem_status_lookup_value = 'Closed' or
wi.workitem_status_lookup_value = 'Resolved') and
(datepart(mm,wi.workitem_created_on)=datepart(mm,g etdate()))) as "Closed",
STR(ROUND(
(convert(decimal,@.Closed) /
convert(decimal,@.Total) * 100),2),10,2) + ' %' AS 'Percent Closed'

FROM dbo.hd_workitem_current_view as workitem
GROUP BY workitem.assigned_to_worker_location|||select Site
, Created
, Open
, Closed
, round(100.0 * Closed / Total,2) as Percent_Closed
from (
select workitem.assigned_to_worker_location as Site
, sum(case when datepart(mm,wi.workitem_created_on)
=datepart(mm,getdate())
then 1 else 0 end ) as Created
, sum(case when wi.workitem_status_lookup_value
in ('Open','Wait-User','Wait-Vendor'
,'Wait-Part','Requested')
then 1 else 0 end ) as Open
, sum(case when wi.workitem_status_lookup_value
in ('Closed', 'Resolved')
and datepart(mm,wi.workitem_created_on)
=datepart(mm,getdate())
then 1 else 0 end ) as Closed
, count(*) as Total
from dbo.hd_workitem_current_view as workitem
group
by workitem.assigned_to_worker_location
) as d|||Thank you much! That worked except for the % calculation. For example, I have 22 items created and 22 items closed, that should be 100%, but it shows 6%.|||whoops, you are rightselect Site
, Created
, Open
, Closed
, round(100.0 * Closed
/ ( select count(*)
from dbo.hd_workitem_current_view )
,2) as Percent_Closed
from (
select workitem.assigned_to_worker_location as Site
, sum(case when datepart(mm,wi.workitem_created_on)
=datepart(mm,getdate())
then 1 else 0 end ) as Created
, sum(case when wi.workitem_status_lookup_value
in ('Open','Wait-User','Wait-Vendor'
,'Wait-Part','Requested')
then 1 else 0 end ) as Open
, sum(case when wi.workitem_status_lookup_value
in ('Closed', 'Resolved')
and datepart(mm,wi.workitem_created_on)
=datepart(mm,getdate())
then 1 else 0 end ) as Closed
from dbo.hd_workitem_current_view as workitem
group
by workitem.assigned_to_worker_location
) as d|||Thanks again, worked great!