SQL Server SQL Max(integer) without group by

xyhw6mcr 于 2023-05-05 发布在其他

关注(0)|答案(2)|浏览(131)

I have large tables where data quality rules are running. There is a field called "RunNumber" to identify if data is appended- it can have a value of 1 or 2(but not limited to just 2). We don't have a Group By statement. Searching here, I found questions titled "SQL Max(date) without group by" and "SQL max without group by", and tried putting the MAX statement as below, but don't think that it's working correctly because of the output. Thanks in advance!

SELECT
    MAX(RunNumber),
    CAST(SUM(FirstName) AS INT) AS FirstName,
    CAST(SUM(LastName) AS INT) AS LastName,
    CAST(SUM(State) AS INT) AS State
FROM
    (SELECT
         CASE WHEN FirstName IS NULL THEN 1 ELSE 0 END FirstName,
         CASE WHEN LastName IS NULL THEN 1 ELSE 0 END LastName,
         CASE WHEN State IS NULL THEN 1 ELSE 0 END State
     FROM 
         Table.Customers (nolock)
     WHERE 
         DateOfRecord = '5/1/2023') a;

The output that I get is:

[No name 1]	FirstName	LastName	State
0	5000	7500	9000

sql-server

来源：https://stackoverflow.com/questions/76157951/sql-maxinteger-without-group-by

2条答案

按热度按时间

bwleehnv1#

Seems like a conditional aggregation should do the trick

Select MaxRun    = MAX(RunNumber)
      ,FirstName = sum( case when FirstName is null then 1 else 0 end)
      ,LastName  = sum( case when LastName  is null then 1 else 0 end)
      ,[State]   = sum( case when [State]   is null then 1 else 0 end)
 From Table.Customers
 Where DateOfRecord = '2023-05-01'

赞(0）回复(0）举报 2023-05-05

ttygqcqt2#

I understood something from your question, you want to do calculations for each RunNumber

SELECT
        RunNumber,
         FirstName,
         LastName,
         State

FROM
(
    SELECT
            SUM(CASE WHEN FirstName IS NULL THEN 1 ELSE 0 END) over( partition by RunNumber order by (select 1)) FirstName,
            SUM(CASE WHEN LastName IS NULL THEN 1 ELSE 0 END) over( partition by RunNumber order by (select 1)) LastName,
            SUM(CASE WHEN State IS NULL THEN 1 ELSE 0 END) over( partition by RunNumber order by (select 1)) State
            ,RunNumber 
            ,ROW_NUMBER()  over( partition by RunNumber order by (select 1)) as rw
    FROM Customers (nolock)
    WHERE DateOfRecord = '5/1/2023'
)a
where a.rw=1

Base Data:

create table Customers(FirstName nvarchar(100),LastName nvarchar(100),State int,
DateOfRecord date,RunNumber int)
insert into Customers values('aa',null,null,'5/1/2023',0)
,(null,'bb',null,'5/1/2023',0),(null,'tt',null,'5/1/2023',1),('ss',null,null,'5/1/2023',1)

赞(0）回复(0）举报 2023-05-05

我来回答

SQL Server SQL Max(integer) without group by

2条答案

相关问题

热门标签

最新问答