Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
T-SQL Count If

Answered T-SQL Count If

  • Saturday, February 02, 2013 8:27 PM
     
     
    Sign In to Vote
    0

    I have 2 tables of data.

    Table 1 contains "TransactionID" and Table 2 contains the details of those transactions, such as "Item", "Sales", etc.  The join key for these 2 tables is on "TransactionID".

    There are a couple things I would like to do.

    1) Find the distinct number of "TransactionID's" that contain Item's starting with ABCD.

    2) Find the distinct number of "TransactionID's" that only contain Item's starting with ABCD.  I'm trying to find see what customers buy with particular items.  The goal of this part is to find the number of transactions where customers had a "naked ticket".

    Any help would be appreciated.  Thanks.

     

All Replies

  • Saturday, February 02, 2013 9:10 PM
     
     Answered Has Code
    Sign In to Vote
    0
     
    SELECT COUNT(*) 
FROM   Transactions T
WHERE  EXISTS (SELECT *
               FROM   TransactionsDetails TD 
               WHERE  T.TransactrionID = TD.TransactionID
                 AND  TD.Item LIKE 'ABCD%')

SELECT COUNT(*) 
FROM   Transactions T
WHERE  EXISTS (SELECT *
               FROM   TransactionsDetails TD 
               WHERE  T.TransactrionID = TD.TransactionID
                 AND  TD.Item LIKE 'ABCD%')
  AND  NOT EXISTS (SELECT *
                   FROM   TransactionsDetails TD 
                   WHERE  T.TransactrionID = TD.TransactionID
                     AND  TD.Item NOT LIKE 'ABCD%')

    An assumption here is that TD.Item does not permit NULL.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked As Answer by MikeD1224 Sunday, February 03, 2013 2:08 AM
    •  
     
  • Sunday, February 03, 2013 7:48 AM
     
      Has Code
    Sign In to Vote
    1

    Hi

    CASE Statement in a SQL SELECT Clause for example:

    DECLARE @TestVal int
SET @TestVal = 5
SELECT
	CASE
		WHEN @TestVal <=3 THEN 'Top 3'
		ELSE 'Other'
	END

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/