get count and column value from two tables for a report

2018-06-30 21:56:18

I need to select the counts from two tables that share a common column which is the clientId and group the totals by the clientIds for a given day and the dateadded column in both tables is in the datetime format.

For example the result should show:

ClientId     Total1 Total2  
aaaa           1      2
bbbbb          43     45
ccccc          123    355

etc. for 2011-03-25

what I currently have is

select 
   (select clientid,count(*) as total1 from TFeed where dateadded = getdate() 
    group by clientId),
   (select clientId, count(*) as total2 from WFeed where dateadded = getdate() 
    group by clientid)

This is of course wrong. ERROR: only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Also for consideration, these are very large tables - over 3 million records and growing. Any help appreciated

EDIT:

about the time - if dateadded = '2011-03-25 12:00:34 0011', how can I compare the time to get dateadded = @getdate() and select all the records for today.

while my query is still running - off topic question...since this is a reporting query and I want to run it regularly to update totals so that when client opens web page or clicks on report it would deliver totals without having to run the query and just select the last totals from a database. do I then need to a have a different query or run this every hour or so.

You're close. You want to join the two tables together before you do the aggregation

select 
    clientid,
    sum(case when t.clientid is not null then 1 else 0 end ) as total1 
    sum(case when w.clientid is not null then 1 else 0 end ) as total1 
    from TFeed t FULL OUTER JOIN WFeed w 
    where w.dateadded = getdate() or t.dateadded = getdate()

This might not be quite what you're going for, but that's the general idea. This also handles the case where there's no data in one of the tables for a particular day.

select tf.clientid,
       SUM( case when tf.dateadded = getdate() then 1 else 0 end) as Total1,
       SUM( case when wf.dateadded = getdate() then 1 else 0 end) as Total2
from tfeed tf full outer join wfeed wf on tf.clientid = wf.clientid
group by tf.clientid

尝试一下这样的事情。

select clientid, sum(total1) as total1, sum(total2) as total2
from
(select clientid,count(*) as total1, 0  as total2 
from TFeed 
where dateadded = @someDate
group by clientId
UNION
select clientId, 0  as total1, count(*) as total2 
from WFeed 
where dateadded = @someDate
group by clientid)
group by clientid

链接地址: http://www.djcxy.com/p/86226.html

上一篇: 内部连接与十字连接对十字应用

下一篇: 从报表的两个表中获取计数和列值