asked on
Select count() from <2 tables>
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?
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?
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
Last Comment
jorge_toriz
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
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
ASKER
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'.
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'.
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
select user2ent, count(*) as hits from sop10100
group by user2ent
union all
select user2ent, count(*) from sop30200
group by user2ent
order by user2ent asc
ASKER
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.
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.
ASKER
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
The subquery is the right solution
group by user2ent
union all
select user2ent, count(*) as hits from sop30200
group by user2ent
order by user2ent asc