| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-13-05, 15:45
|
|
Registered User
|
|
Join Date: Dec 2005
Posts: 2
|
|
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 !!!
Carlos
|
|
12-13-05, 16:39
|
|
Registered User
|
|
Join Date: Jun 2003
Location: West Palm Beach, FL
Posts: 2,455
|
|
Use SUM and DECODE:
Code:
SELECT field1, SUM(field2), SUM(DECODE(field3,1,1,0))
WHERE blah blah FROM table GROUP BY field1
__________________
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
|
|
12-13-05, 18:25
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,605
|
|
|
|
I'd use SQL to achieve the same purpose. Something like:
Code:
SELECT col1, Sum(col2), Sum(CASE WHEN 1 = col3 THEN 1 END)
FROM myTable
WHERE blah
GROUP BY col1
Bear 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:
Code:
SELECT col1, Sum(col2), (SELECT Count(*) FROM myTable AS z WHERE 1 = z.col3)
FROM myTable
WHERE blah
GROUP BY col1
-PatP
|
|
12-13-05, 19:24
|
|
Registered User
|
|
Join Date: Dec 2005
Posts: 2
|
|
Thanks a lot !!!
It works great !!..
Carlos
|
|
11-17-06, 08:54
|
|
Registered User
|
|
Join Date: Nov 2006
Posts: 3
|
|
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
|
|
11-17-06, 09:35
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
Code:
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
|
|
11-17-06, 09:58
|
|
Registered User
|
|
Join Date: Nov 2006
Posts: 3
|
|
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%.
|
|
11-17-06, 10:04
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
whoops, you are right
Code:
select 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
|
|
11-17-06, 12:02
|
|
Registered User
|
|
Join Date: Nov 2006
Posts: 3
|
|
Thanks again, worked great!
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Conditional Count(*)
