SQL Count function count all rows if a condition is met

You can solve this problem in two steps:

1. Write a query that returns the relevant customers' IDs. If you write that query as a subquery or a CTE (common table expression, i.e. a WITH clause), you don't even need to put the results in a temporary table.

2. Join the resulting table from (1) to the transaction table (to filter out all transactions that you're not interested in), then group by customer ID (so that you can use aggregate functions in the SELECT clause), and select the COUNT(DISTINCT TransactionId).

Something along these lines:

WITH relevantCustomers (CustomerId) AS
(
    SELECT DISTINCT CustomerId
    FROM Transactions
    WHERE ItemId = 123
)
SELECT t.CustomerId, COUNT(DISTINCT t.TransactionId)
FROM Transactions t
INNER JOIN relevantCustomers rc ON t.CustomerId = rc.CustomerId
GROUP BY t.CustomerId

This gives you a set of all customers (by their ID) that bought item 123, along with the total number of transactions for each of these.

If you are interested in only one specific customer, you can add a WHERE clause to the "outer" query that filters by that customer's ID.

select count(*) from Transaction where CustID in (select CustID from Transaction where CustID = 123 and ItemID = 456)

If customer 123 hasn't been involved in a transaction for item 456, then the query will return a count of 0 because the subquery had no custid entries in it.

Thank you Jim Reminding me about the WHERE something is IN (NESTED QUERY) got me on the right track.

I have come up with the following answer to my problem:

SELECT Name, Count(Name)
FROM customer c
JOIN transaction t
ON c.ID = t.CustID
WHERE CustID IN (SELECT CustID 
FROM transaction
WHERE ItemID = 2);

Apologies to Stakx, I was looking for an answer at the same time you were obviously providing one.

For "If a particular customer has participated in a transaction which involves a given ItemID" - You mean you have the customer ID and product ID ?If that is the case you can just write -

SELECT * FROM TRANSACTION 
where CUSTID={"your customer id"} 
and ItemID = {"Required Item ID"} ;

For second part you can try something like this :

Select count(*) from TRANSACTION 
where CUSTID={"your customer id"} 
and ItemID = {"Required Item ID"} ;

You can play around SQL queries here to understand SQL better .

Regards Paritosh