sql server - get count and column value from two tables for a report

Question

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.

score 1 · Accepted Answer · answered Mar 25 '11 at 16:33

1

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.

answered Mar 25 '11 at 16:33

dfb

13,133
2
31
52

thanks executed it but it is still running - is it unusual to take this long...almost an hour...like i said about 4 million records in both tables – vbNewbie Mar 25 '11 at 17:34
Do you have indicies on clientid? – dfb Mar 25 '11 at 17:39
This query will take quite a long time if this isn't in place on both tables – dfb Mar 25 '11 at 17:52
still running and do not want to cancel – vbNewbie Mar 25 '11 at 17:54
It will likely run more quickly if you stop and add the indicies, your call though – dfb Mar 25 '11 at 17:57
do I just add a normal index to that column or is something else to specify – vbNewbie Mar 25 '11 at 18:15
Nope, just add an index on (clientid) for each of the tables. – dfb Mar 25 '11 at 18:16

Sachin Shanbhag · Answer 2 · 2011-03-25T16:41:36.580

1

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

edited Mar 25 '11 at 16:41

answered Mar 25 '11 at 16:33

Sachin Shanbhag

54,530
11
89
103

1

The inner join will eliminate records that are not in both tables. Also the way you built the query it will iterate all the records evaluating the case statement and doing a sum and ignoring the records that don't fall in the date criteria. – DidierDotNet Mar 25 '11 at 16:40

score 0 · Answer 3 · answered Mar 25 '11 at 16:37

Try some thing like this.

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

score 0 · Answer 4 · edited May 23 '17 at 12:26

Using a cross apply is really helpful and speeds things up dramatically. I'm not positive on your associations and whether or not you've got a one-to-one relationship between clientIDs in your 2 feeds, but this is the direction you would want to go for optimizing your query against so many records.

Also, here's a link to another discussion with more information on the topic of cross apply that may be helpful if this exact query isn't working for you:

When should I use Cross Apply over Inner Join?

 with x as (
    select clientID as clientID, COUNT(*) as total1
    from TFeed where dateadded = GETDATE()
    group by clientID
    )
    select x.clientID,x.total1,y.total2 from x
    cross apply
    (
        select clientID as clientID, COUNT(*) as total2
        from WFeed where dateadded = GETDATE()
        group by clientID
    ) y

sql server - get count and column value from two tables for a report

4 Answers4