SQL Server Add column name with COALESCE

hfyxw5xn 于 2023-11-16 发布在其他

关注(0)|答案(1)|浏览(146)

I have this stored procedure, and I want to add a column name to the value I'm getting. Where should I add the
as "TotalNewspapers"

(or whatever I have to add) if Im using the COALESCE?

SELECT COALESCE ((SELECT 
  count(distinct Appendage.AppendageName) as "TotalNewspapers" 
  FROM Edition
   inner join SourceInformation on (SourceInformation.SourceInformationID =  Edition.SourceInformationID)
   left join Appendage on (Appendage.AppendageID = Edition.AppendageID)
   inner join Pages on (Edition.EditionID = Pages.EditionID)
  where 
    Edition.publishdate >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
    and Edition.publishdate <= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)+1 
    and Pages.PullBy is null
    and Edition.FinishDate is null
group by Appendage.AppendageName, SourceInformation.SourceInformationName, Pages.PullBy, Edition.FinishDate, Appendage.AppendageID
having count(Pages.PullBy) > 1) , 0);

sql-server

来源：https://stackoverflow.com/questions/38590682/add-column-name-with-coalesce

1条答案

按热度按时间

pxy2qtax1#

You can use ISNULL or COALESCE :

SELECT ISNULL(( --OR COALESCE
    SELECT count(distinct a.AppendageName) as [TotalNewspapers]
    FROM Edition e
    inner join SourceInformation s
        on (s.SourceInformationID =  e.SourceInformationID)
    left join Appendage a
        on (a.AppendageID = e.AppendageID)
    inner join Pages p
        on (e.EditionID = p.EditionID)
    where 
        e.publishdate >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
        and e.publishdate <= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)+1 
        and p.PullBy is null
        and e.FinishDate is null
    group by a.AppendageName, s.SourceInformationName, p.PullBy, e.FinishDate, a.AppendageID
    having count(p.PullBy) > 1
),0) as [TotalNewspapers]

I remove sub-query, all you need can be done in one select statement. Also add aliases .

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server Add column name with COALESCE

1条答案

相关问题

热门标签

最新问答