CODEWITHSUNDEEP
sqlsql-servert-sql
Jerry C
Jerry C

Reputation: 181

adding two columns together to get a sum from a union query

I have the following query:

 with abby as (SELECT u.Name as 'UserId1'
, count(distinct b.id) as 'Total Count'
, '' as 'ediCount'

FROM abprot.[FC10y].[dbo].[Batch] b with(nolock)

inner join abprot.[FC10y].[dbo].[Principal] u with(nolock) on u.Id = b.CreatorId

where b.CreationDate >= getdate() - 7 
and u.name <> 'abbyyservice'

group by u.Name)

,  edimon as (select userId
, '' as 'Total Count'
, count(*) as 'esubCount'

from ESubmitTrackingTBL

where DateCopied >= getdate() - 7
    and userid <> abbyyservice

group by UserId
)

select * from abby union all select * from edimon

I need to sum the totals from each cte into another field by user. I have tried to include another cte but get a warning that 'UserID' and 'Total Count' are specified more than once.
If I just run the query as unions, the result is shown below: enter image description here

End result should look like the following:

enter image description here

If I have left anything out that may help, my apologies -- please let me know what I can add to clarify this if need be.

Upvotes: 0

Views: 856

Answers (3)

Nicholas Carey
Nicholas Carey

Reputation: 74297

I'd dispense with the CTEs and just use a derived table.

To avoid problems when query or stored procedure executions cross date boundaries, I prefer to use T-SQL variables and establish a consistent sense of Now-ness for the entire run. Crossing a midnight boundary and having "today" change in mid-run can cause ... subtle problems, if you're not careful about things. Don't ask me how I know this.

So...

declare @now         datetime = current_timestamp      -- current instant in time
declare @today       date     = @now                   -- today's date
declare @cutoff_date date     = dateadd(day,-7,@today) -- 1 week ago

Your cutoff_date value might vary depending on whether your spec requires looking at the previous 7 calendar days or the previous 168 hours (7*24) relative to the current moment in time).

So my query would then look something like this:

declare @now         datetime = current_timestamp      -- current instant in time
declare @today       date     = @now                   -- today's date
declare @cutoff_date date     = dateadd(day,-7,@today) -- 1 week ago

select user_id        =      x.user_id       ,
       total_count    = sum( x.total_count ) ,
       edi_count      = sum( x.edi_count   ) ,
       grand_total    = sum( x.total_count )
                      + sum( x.edi_count   ) ,
       esub_count_pct = 100.0
                      * sum( x.edi_count )
                      / (   sum( x.total_count )
                          + sum( x.edi_count   )
                        )
from (   select user_id     = u.Name ,
                total_count = count( distinct b.id ) ,
                esub_count  = 0
         from abprot.FC10y.dbo.Batch     b
         join abprot.FC10y.dbo.Principal u on u.Id   =  b.Creator.Id
                                          and u.name <> 'abbyyservice'
         where b.CreationDate >= @cutoff_date
         group by u.Name
       UNION ALL
         select user_id     = t.userId ,
                total_count = 0        ,
                esub_count  = 1
         from dbo.ESubmitTrackingTBL t
         where t.DateCopied >= @cutoff_date
     ) x
group by x.user_id

Upvotes: 1

Jerrad
Jerrad

Reputation: 5290

with abby as (
      SELECT u.Name as UserId, count(distinct b.id) as [Total Count], '' as ediCount
      FROM abprot.[FC10y].[dbo].[Batch] b with(nolock) inner join
           abprot.[FC10y].[dbo].[Principal] u with(nolock)
           on u.Id = b.CreatorId
      where b.CreationDate >= getdate() - 7 and u.name <> 'abbyyservice'
      group by u.Name
    ), 
    edimon as (
     select userId, '' as [Total Count], count(*) as esubCount
     from ESubmitTrackingTBL
     where DateCopied >= getdate() - 7 and userid <> 'abbyyservice'
    )

select UserId, 
  TotalCount, 
  esubCount, 
  convert(decimal, esubCount)/(convert(decimal, TotalCount) + convert(decimal, esubCount)) percentesubCount
from (select * from abby
      union
      select * from edimon) x

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270181

Your query did look chopped off. I assume that you just want what is in the last CTE.

The calculation is pretty simple. I'm a bit surprised that someone who could put together this SQL wouldn't figure out the final calculation:

with abby as (
      SELECT u.Name as UserId, count(distinct b.id) as [Total Count], '' as ediCount
      FROM abprot.[FC10y].[dbo].[Batch] b with(nolock) inner join
           abprot.[FC10y].[dbo].[Principal] u with(nolock)
           on u.Id = b.CreatorId
      where b.CreationDate >= getdate() - 7 and u.name <> 'abbyyservice'
      group by u.Name
    ), 
    edimon as (
     select userId, '' as [Total Count], count(*) as esubCount
     from ESubmitTrackingTBL
     where DateCopied >= getdate() - 7 and userid <> abbyyservice
    )
select userId, [Total Count], esubcount,  ([Total Count] + esubcount) as Total,
       100.0 * esubcount / ([Total Count] + esubcount) as [percent edisubcount] 
from edimon;

A word of advice: avoid using single quotes for identifiers. It is preferable to use names (such as userid) that do not need to be escaped. If you do, then use square braces.

EDIT:

Your problem is probably easier than you are making it out to be. Try using group by with rollup:

SELECT u.Name as UserId, count(distinct b.id) as [Total Count], '' as ediCount,
        ([Total Count] + esubcount) as Total,
       100.0 * esubcount / ([Total Count] + esubcount) as [percent edisubcount] 
FROM abprot.[FC10y].[dbo].[Batch] b with(nolock) inner join
     abprot.[FC10y].[dbo].[Principal] u with(nolock)
     on u.Id = b.CreatorId
where b.CreationDate >= getdate() - 7 and u.name <> 'abbyyservice'
group by u.Name with rollup;

Upvotes: 0

Related Questions