Fetch rows and count them in sqlserver

Question

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?

Gordon Linoff · Accepted Answer · 2017-08-28 06:22:11Z

1

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;

answered Aug 28, 2017 at 6:22

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Makyen · Accepted Answer · 2018-04-19 22:02:55Z

1

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;

edited Apr 19, 2018 at 22:02

Makyen♦

33.6k12 gold badges94 silver badges128 bronze badges

answered Aug 28, 2017 at 6:13

Radim Bača

10.7k1 gold badge21 silver badges35 bronze badges

Comments

Makyen · Accepted Answer · 2018-04-19 22:03:49Z

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.

Collectives™ on Stack Overflow

Fetch rows and count them in sqlserver

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related