Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2005
    Posts
    2

    Question Unanswered: 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

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    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

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  4. #4
    Join Date
    Dec 2005
    Posts
    2
    Thanks a lot !!!

    It works great !!..

    Carlos

  5. #5
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    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%.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2006
    Posts
    3
    Thanks again, worked great!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •