CODEWITHSUNDEEP
sql-servercount
Chase Ernst
Chase Ernst

Reputation: 1155

SQL Server count of column not contained in aggregate function

I am having quite the issue in creating the correct query for SQL Server. To start off, here is the data that I am trying to query.

TicketBookNum | TicketNum | Used | DateIssued | UnitID
---------------------------------------------
1234          |   25      |  Yes | Dec 18 2015 | 101
----------------------------------------------     
1234          |   26      |  Yes | Dec 18 2015 | 101
----------------------------------------------         
1234          |   27      |  Yes | Dec 18 2015 | 101
----------------------------------------------     
1234          |   28      |  No  | Dec 18 2015 | 101
----------------------------------------------     
1235          |   70      |  Yes | Dec 10 2015 | 102   
----------------------------------------------     
1235          |   71      |  Yes | Dec 10 2015 | 102    
----------------------------------------------        
1235          |   72      |  No  | Dec 10 2015 | 102    
----------------------------------------------       
1235          |   73      |  No  | Dec 10 2015 | 102   
----------------------------------------------

So am querying this data to put into a list view. TicketBookNum and TicketNum are the primary key combo. The query I am using to retrieve the data is a search function and it is this:

create procedure [dbo].[SearchTicketBooks]
@TicketBookNumber   nvarchar(10) = null,
@DateIssuedA        datetime = null,
@DateIssuedB        datetime = null,
@UnitID         nvarchar(10) = null
as
select distinct(TicketBookNum), DateIssued, U.UnitID, COUNT(Used) as 'Completed' --Error on this COUNT because there is no aggregate function
   from TicketBooks TB
    full outer join Units U
    on TB.UnitID=U.UnitID
where TicketBookNum = 
    (case when @TicketBookNumber is null or @TicketBookNumber = '' then TicketBookNum else @TicketBookNumber end)
and TB.UnitID = 
    (case when @UnitID is null or @UnitID = '' then TB.UnitID else @UnitID end)
and DateIssued = 
    (case when (@DateIssuedA is null and @DateIssuedB is null) or (@DateIssuedA = '' and @DateIssuedB = '') or 
     @DateIssuedB = @DateIssuedA or DateIssued between @DateIssuedA and @DateIssuedB then DateIssued else @DateIssuedA end)
order by DateIssued desc    
go

So what this query does is lets the user search for the Ticket Books by selecting a date range, the unit, or searching the direct ticketbook number. If nothing is selected in this search criteria, it returns all of the values.

I am trying to return the count for column Used where it equals Yes, and only for that Ticket book. So the desired result for no search criteria would be:

TicketBookNum | DateIssued | UnitID | Completed
-----------------------------------------------
1234          | Dec 18 2015| 101    |  3
1235          | Dec 10 2015| 102    |  2

I have been working on this for quite a while and I cannot figure it out. Hopefully I have supplied enough info, and any help will be greatly appreciated.

Upvotes: 0

Views: 56

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

Use this for your Completed column, and GROUP BY the other columns:

SUM(CASE Used WHEN 'Yes' THEN 1 ELSE 0 END) AS Completed

Upvotes: 2

Related Questions