在 SQL 中使用 count 函数的问题

发布于 2024-12-07 17:38:13 字数 1090 浏览 0 评论 0原文

我正在运行此查询来获取用户预订的带保险的销售总数和不带保险的销售总数。然而，由于某种原因，所有用户都获得相同的计数。我如何更改查询以显示每个用户的总计。

我想要的基本上是计算出每个用户有多少次预订（有或没有保险销售）

Bookingby 是代理

，T0 是包含有关不包含保险的预订信息的表

，t1 是表包含有关保险预订的信息

，而两个表都提供相同的信息，我如何从两个表中获取每个代理的预订总数

 SELECT   t0.BookedBy, count(t0.resnumber) as NonInsurance, COUNT(t1.resnumber) as Insurance  
    FROM (SELECT BookedBy, ResNumber, DATEPART(year, BookDate) AS Year, DATEPART(month, BookDate) AS month
              FROM dbo.ResGeneral
              WHERE ResNumber NOT IN (SELECT ResNumber FROM dbo.ResItinerary_insurance) 
                  and  ResStatus = 'a'
              GROUP BY BookedBy, ResNumber, BookDate) t0 
        left JOIN (SELECT BookedBy, ResNumber, DATEPART(year, BookDate) AS Year, DATEPART(month, BookDate) AS month
                       FROM dbo.ResGeneral
                       WHERE ResNumber IN (SELECT ResNumber FROM dbo.ResItinerary_insurance)
                           and  ResStatus = 'a') t1         
            ON t1.year = t0.year         
    group by t0.bookedby

原文

I am running this query to get a count of the bookedby users total number of sales with insurance and total number of sales without insurance. However, all of the users are getting the same count for some reason. how can i change my query to show each users totals instead.

what i want is basically to figure out how many bookings each user had with and without insurance sales

bookedby is the agent

and T0 is the table that includes the information about bookings that do not include insurance

and t1 is the table that includes information about bookings with insurance

while both tables provide the same information how can i get a total by booked by for each agent from both tables

 SELECT   t0.BookedBy, count(t0.resnumber) as NonInsurance, COUNT(t1.resnumber) as Insurance  
    FROM (SELECT BookedBy, ResNumber, DATEPART(year, BookDate) AS Year, DATEPART(month, BookDate) AS month
              FROM dbo.ResGeneral
              WHERE ResNumber NOT IN (SELECT ResNumber FROM dbo.ResItinerary_insurance) 
                  and  ResStatus = 'a'
              GROUP BY BookedBy, ResNumber, BookDate) t0 
        left JOIN (SELECT BookedBy, ResNumber, DATEPART(year, BookDate) AS Year, DATEPART(month, BookDate) AS month
                       FROM dbo.ResGeneral
                       WHERE ResNumber IN (SELECT ResNumber FROM dbo.ResItinerary_insurance)
                           and  ResStatus = 'a') t1         
            ON t1.year = t0.year         
    group by t0.bookedby

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

℉服软 2024-12-14 17:38:13

我认为这个查询是等效的：

SELECT g.BookedBy,
       SUM(CASE WHEN i.ResNumber IS NULL THEN 1 ELSE 0 END) AS NonInsurance,
       SUM(CASE WHEN i.ResNumber IS NOT NULL THEN 1 ELSE 0 END) AS Insurance
    FROM dbo.ResGeneral g
        LEFT JOIN dbo.ResItinerary_insurance i
            ON g.ResNumber = i.ResNumber
    WHERE g.ResStatus = 'a'
    GROUP BY g.BookedBy;

I think this query is equivalent:

SELECT g.BookedBy,
       SUM(CASE WHEN i.ResNumber IS NULL THEN 1 ELSE 0 END) AS NonInsurance,
       SUM(CASE WHEN i.ResNumber IS NOT NULL THEN 1 ELSE 0 END) AS Insurance
    FROM dbo.ResGeneral g
        LEFT JOIN dbo.ResItinerary_insurance i
            ON g.ResNumber = i.ResNumber
    WHERE g.ResStatus = 'a'
    GROUP BY g.BookedBy;

回复收藏 0 原文