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!

Concatenating columns with datatype ntext

How can I concatenate 2 columns, both of type ntext, in a select statement?
I get the following error.
"Invalid operator for datatype. Operator equals add, type equals ntext"You cannot use concatenation on TEXT datatype. If the length of the value is
less than 8000, you can use the CAST function to change it to VARCHAR
datatype and do the concatenation. Otherwise, you'll have to return them as
distinct values to the client and leverage the client's string concatenation
capabilities.
Anith|||Thanks, Anith, for your reply.
Actually I was trying some data transfer between 2 databases, so the client
option is not feasible for me and length of some column values is more than
a
varchar would permit, so that's not an option either. But knowing it is not
possible is good enough, so I can think of something else.
"Anith Sen" wrote:

> You cannot use concatenation on TEXT datatype. If the length of the value
is
> less than 8000, you can use the CAST function to change it to VARCHAR
> datatype and do the concatenation. Otherwise, you'll have to return them a
s
> distinct values to the client and leverage the client's string concatenati
on
> capabilities.
> --
> Anith
>
>|||You might also want to look at the UPDATETEXT function, but you'll have to
massage each row at a time. The general workaround is to get multiple
VARCHAR columns back like:
SELECT SUBSTRING( textcol, ( 0 * 8000 ) + 1, 8000 ),
SUBSTRING( textcol, ( 1 * 8000 ) + 1, 8000 ),
SUBSTRING( textcol, ( 2 * 8000 ) + 1, 8000 ),
...
Also in SQL 2005 you will have character types with much better
capabilities.
Anith