CODEWITHSUNDEEP
sqlsql-serverstored-procedures
masoud amirian
masoud amirian

Reputation: 41

Fetch rows and count them in sqlserver

I wrote a stored procedure that join three tables to fetch province title from it's table. This is my code: 

BEGIN
    select TbProvince.title, count(TbProvince.title) as cnt
    from TbProvince
    where TbProvince.provinceId IN (select TbCustomerUser.provinceId 
                                    from TbCustomerUser INNER JOIN
                                         TbDeals
                                         on TbCustomerUser.UserId = TbDeals.sellerUserID
                                    where TbDeals.buyerUserID = 1
                                   )
    group by TbProvince.title
end

Description: I have three tables for deals, customers and provinces. I want to retrieve province title and the count of that for customers that were sellers. The above code have no problem, but only return 1 as a count. The number of customers is more than one.
Can anybody help me solve my problem?

Upvotes: 0

Views: 157

Answers (3)

Esteban P.
Esteban P.

Reputation: 2809

It should be as simple as:

You won't need the subselect. Just join all three tables and you'll receive your desired result.

    SELECT TbProvince.title, 
           count(TbProvince.title) as cnt 
      FROM TbProvince
     INNER JOIN TbCustomerUser 
        ON TbProvince.provinceId = TbCustomerUser.provinceId
     INNER JOIN TbDeals  
        ON TbCustomerUser.UserId = TbDeals.sellerUserID
       AND TbDeals.buyerUserID   = 1
  GROUP BY TbProvince.title

Why did your solution not work? You subselect will return a "list" of provinceIDs from TbCustomerUser combinated with TbDeals with your restriction TbDeals.buyerUserID = 1. The outer select will now return all rows from TbProvince IN this list. But it's not returning a row for each Customer who had a deal. That's why you have to JOIN all three tables at once.

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

You have to perform the JOIN with customer table. If you use semi join (expressed by IN construct in your case) then you avoid duplicates that are expected in your case.

SELECT TbProvince.title,
       COUNT(TbProvince.title) AS cnt
FROM TbProvince 
JOIN TbCustomerUser ON TbProvince.provinceId = TbCustomerUser.provinceId
JOIN TbDeals ON TbCustomerUser.UserId = TbDeals.sellerUserID
WHERE TbDeals.buyerUserID = 1
GROUP BY TbProvince.title;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Your query is filtering the rows of TbProvince and then aggregating that table -- and only that table.

Instead, you want to join the tables together to count the customers not the provinces. The query is much simpler to write and read if you use table aliases:

select p.Title, count(*)
from TbCustomerUser cu join
     TbDeals d
     on cu.UserId = d.sellerUserID join
     TbProvince p
     on p.provinceId = cu.provinceId
where d.buyerUserID = 1
group by p.Title;

Upvotes: 1

Related Questions