We help IT Professionals succeed at work.

Select count() from <2 tables>

656 Views
Last Modified: 2008-07-18
I need to run the following statements and get 1 set of results:

select user2ent, count(*) as hits from sop10100
group by user2ent
order by user2ent asc

select user2ent, count(*) as hits from sop30200
group by user2ent
order by user2ent asc

I want to basically have a statement that says:


select user2ent, count(*) as hits from sop10100 AND SOP30200
group by user2ent
order by user2ent asc

Any ideas?
Comment
Watch Question

CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
select user2ent, counter = count(*) as hits from sop10100
group by user2ent
union all
select user2ent, count(*) as hits from sop30200
group by user2ent
order by user2ent asc
jorge_torizResearch & Development Manager

Commented:
Try with this:

SELECT user2ent, COUNT(*)
FROM (
    SELECT user2ent, COUNT(*) AS _count
    FROM sop10100
    UNION
    SELECT user2ent, COUNT(*)
    FROM sop30200
) AS TempCount
GROUP BY user2ent
ORDER BY user2ent ASC

Author

Commented:
chapmandew this is the message I receive:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'hits'.

Jorge_Toriz this is the message I receive:
Msg 8120, Level 16, State 1, Line 1
Column 'sop10100.USER2ENT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'TempCount'.
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
ahh...didn't see that you had already given it an alias:

select user2ent, count(*) as hits from sop10100
group by user2ent
union all
select user2ent, count(*) from sop30200
group by user2ent
order by user2ent asc

Author

Commented:
Ok that is definately getting closer....The only problem now is that the group by gives me duplicate user2ent (from each table).  

Example....rwilson is in both tables but I get an rwilson, count for sop10100 and rwilson, count for sop30200.  I want to combine rwilson and the counts to see the total counts for rwilson (user2ent) in both tables.

Author

Commented:
Here were the results
bsatterfield1  	4
bsatterfield1  	13
bwalker        	73
cbigbee        	8
cbigbee        	13
cmorgan1       	6
ctinsley       	1
mblack         	2
mblack         	19
rwatson1       	3
rwatson1       	5
rwilson1       	2
screef1        	19
screef1        	26
ssimpson       	71

Open in new window

CERTIFIED EXPERT
Awarded 2008
Awarded 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
jorge_torizResearch & Development Manager

Commented:

SELECT user2ent, COUNT(*)
FROM (
    SELECT user2ent, COUNT(*) AS _count
    FROM sop10100
    GROUP BY user2ent
    UNION
    SELECT user2ent, COUNT(*)
    FROM sop30200
    GROUP BY user2ent
) AS TempCount
GROUP BY user2ent
ORDER BY user2ent ASC

Open in new window

jorge_torizResearch & Development Manager

Commented:
The subquery is the right solution
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.