admin管理员组

文章数量:1395225

I have 4 tables tblProducts, tblSellers, tblViewedtimes, tblContactedtimes with below columns. How can we retrieve summary as below:

tblContactedtimes

SellerId CustomerID ProductID ContactedTime
2 3223 26 2024-06-06 20:37:11.6200000
3 2492 24 2024-06-10 05:25:43.1800000
3 2492 24 2024-06-10 05:28:06.7000000
7 2567 21 2024-06-10 22:10:50.3033333
7 3267 21 2024-06-11 05:28:08.4600000
3 3175 24 2024-06-12 20:00:07.4166667

I have 4 tables tblProducts, tblSellers, tblViewedtimes, tblContactedtimes with below columns. How can we retrieve summary as below:

tblContactedtimes

SellerId CustomerID ProductID ContactedTime
2 3223 26 2024-06-06 20:37:11.6200000
3 2492 24 2024-06-10 05:25:43.1800000
3 2492 24 2024-06-10 05:28:06.7000000
7 2567 21 2024-06-10 22:10:50.3033333
7 3267 21 2024-06-11 05:28:08.4600000
3 3175 24 2024-06-12 20:00:07.4166667

tblViewedtimes

CustomerID ProductID ViewedTime
2601 20 2024-05-09 18:56:05.7366667
2468 12 2024-05-09 19:18:46.2100000
2133 21 2024-05-09 20:44:41.6166667
3036 22 2024-05-09 21:38:54.3400000
2019 22 2024-05-09 21:44:22.7200000
2507 26 2024-05-05 19:03:14.6633333
3036 24 2024-05-09 21:38:54.3400000

tblProducts

ProductID ProductName SellerId ClosingTime
20 ProdName 20 2 2025-06-06 20:37:11.6200000
12 ProdName 12 3 2026-05-09 18:56:05.7366667
21 ProdName 21 7 2025-06-06 20:37:11.6200000
22 ProdName 22 7 2026-05-09 18:56:05.7366667
24 ProdName 24 3 2025-06-06 20:37:11.6200000
26 ProdName 26 2 2026-05-09 18:56:05.7366667

tblSellers

SellerId SellerName
2 Seller 2
3 Seller 3
7 Seller 7

Is it possible to query below summary as it involves 2 count, along with where condition "Where tblProducts.ClosingTime > CurrentTime"?

Sample output

ProductName SellerName Count of Product Views Count of Product Contacted
ProdName 20 Seller 2 1 0
ProdName 12 Seller 3 1 0
ProdName 21 Seller 7 1 2
ProdName 22 Seller 7 2 0
ProdName 24 Seller 3 1 3
ProdName 26 Seller 2 1 1
  • sql
  • sql-server
  • join
  • group-by
Share Improve this question edited Mar 13 at 21:56 user1066231 asked Mar 13 at 21:42 user1066231user1066231 6051 gold badge11 silver badges33 bronze badges 3
  • Please add the actual results you are expecting from the sample data provided. – Dale K Commented Mar 13 at 21:44
  • 2 wasn't this asked a couple of hours ago? Like stackoverflow/questions/79506408/… – siggemannen Commented Mar 13 at 21:54
  • @siggemannen just saw that question, and learnt CTE. Thankyou – user1066231 Commented Mar 13 at 22:34
Add a comment  | 

1 Answer 1

Reset to default 2

The simplest way of counting decorrelated items is to do this in subqueries:

select
    ProductName, SellerName,
    (select count(1) from tblViewedtimes v where v.ProductID = p.ProductID) [Count of Product Views],
    (select count(1) from tblContactedtimes c where c.ProductID = p.ProductID) [Count of Product Contacted]
from tblProducts p join tblSellers s on s.SellerId = p.SellerID;

which returns:

ProductName SellerName Count of Product Views Count of Product Contacted
ProdName 12 Seller 3 1 0
ProdName 20 Seller 2 1 0
ProdName 21 Seller 7 1 2
ProdName 22 Seller 7 2 0
ProdName 24 Seller 3 1 3
ProdName 26 Seller 2 1 1

(see it in a fiddle)

If you want to correlate your data (e.g. get the percentage of views that result in a contact),
you'll prefer Common Table Expressions (CTE),
which will allow you to reuse the subselects multiple times (for example get the count as is + use it to compute a percentage).
But that's another story…

本文标签： sql serverRetrieve from 4 SQL tables with two countgroup by valuesStack Overflow

版权声明：本文标题：sql server - Retrieve from 4 SQL tables with two countgroup by values? - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1744683822a2619576.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。