March 21, 2013 at 7:03 am
I need help with the following SQL query:
select whoverified, whochecked AS 'userid', count(*) whoverified, count (*) whochecked
from rxproductivity
join users on rxproductivity.whoverified = users.userid
join users on rxproductivity.whochecked = users.userid
group by whoverified, whochecked
having count(whoverified) > 1
order by count(whoverified) desc
I am trying to count the number of times a particular USERID is shown in the columns named WHOVERIFIED and WHOCHECKED grouping the results by USERID.
USERID is in USERS table
WHOVERIFIED, WHOCHECKED are in RXPRODUCTIVITY table
When I execute the above SQL query, I get this error message:
The objects "users" and "users" in the FROM clause have the same exposed names. Use correlation names to distinguish them.
Any help would be most appreciated.
March 21, 2013 at 7:22 am
aliases are always needed when you add the same table twice; here'a i'm using an aribtrary T1 and T2;
it especially helps the query as well, as with your example, it looks like you are only selecting columns from the rxproductivity table, so it seems like there's no value to joining to the other tables;
maybe you were going to further enhance it to get the names from the other tables?
here's my best guess, without having the tables DDL to confirm anything:
SELECT
R1.whoverified,
R1.whochecked AS 'userid',
T1.Name as VerifiedUser,
T2.Name as WhoCheckedUser,
COUNT(*) whoverified,
COUNT (*) whochecked
FROM rxproductivity R1
JOIN users T1
ON R1.whoverified = T1.userid
JOIN users T2
ON R1.whochecked = T2.userid
GROUP BY
R1.whoverified,
R1.whochecked,
T1.Name,
T2.Name
HAVING
COUNT(R1.whoverified) > 1
ORDER BY
COUNT(R1.whoverified) DESC
Lowell
March 21, 2013 at 7:24 am
Try this:
select whoverified, whochecked AS 'userid', count(*) whoverified, count (*) whochecked
from rxproductivity
join users us1 on rxproductivity.whoverified = us1.userid
join users us2 on rxproductivity.whochecked = us2.userid
group by whoverified, whochecked
having count(whoverified) > 1
order by count(whoverified) desc
March 21, 2013 at 7:45 am
Thanks for the quick reply.
Not sure if I was clear in what I'm trying to accomplish with this query.
What I'm trying to do is count the number of times a USERID appears in the WHOVERIFIED and WHOCHECKED columns of the RXPRODUCTIVITY table and return the results in the following format:
USERID| WHOVERIFIED| WHOCHECKED
12345 51 0
2345 10 0
3456 0 23
If I use this query, I get the correct output but it lacks the WHOCHECKED column:
select whoverified AS 'userid', count(*) whoverified
from rxproductivity
join users
on rxproductivity.whoverified = users.userid
group by whoverified
having count(whoverified) > 1
order by count(whoverified) desc
I really appreciate your help with this.
March 22, 2013 at 1:09 pm
This is probably what you need
SELECTU.userid,
SUM( CASE WHEN U.userid = P.whoverified THEN 1 ELSE 0 END ) AS whoverified,
SUM( CASE WHEN U.userid = P.whochecked THEN 1 ELSE 0 END ) AS whochecked
FROMusers AS U
INNER JOIN rxproductivity AS P ON U.userid = P.whoverified OR U.userid = P.whochecked
GROUP BY U.userid
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 25, 2013 at 8:54 am
Hey thanks.
This works perfectly with what i am trying to do.
The only down side to using this is it does not return results where userid's have 0 records in both whoverified and whochecked columns of the rxproductivity table.
Other than that, it is just what I need.
Thanks for your help.:-)
March 25, 2013 at 11:14 pm
jrseven68 (3/25/2013)
Hey thanks.This works perfectly with what i am trying to do.
The only down side to using this is it does not return results where userid's have 0 records in both whoverified and whochecked columns of the rxproductivity table.
Other than that, it is just what I need.
Thanks for your help.:-)
Try replacing the INNER JOIN with LEFT OUTER JOIN in the query
That should give you the desired results.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 26, 2013 at 5:57 am
I edited the query as you mentioned and it worked.
Now I have additional requirements to be added:
REQ #1: I need to associate usernames instead of UserID
So how would I go about translating the UserID to match the Username?
REQ #2: I need to include a total for QtyRxFilled and QtyRxChecked
Below is how the tables are structured in the database
Users.UserID varchar(10) NOT NULL
RxProductivity.WhoVerified varchar (10) NULL
RxProductivity.Whochecked varchar (10) NULL
Again, I thank you for your assistance with this.
Your help is greatly appreciated.
March 27, 2013 at 3:02 am
We don't have any idea how your tables look like and what are the column names
Can you provide us the DDL of the tables involved along with some sample data and the expected results based on the sample data
That will help us provide you a tested solution back
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 27, 2013 at 6:07 am
Thanks for the attempt at this.
I've been able to incorporate those additional requirements into the query.
For REQ #1: I simply added a 2nd table into the report and that took care of this requirement.
For REQ #2: I ended up changing the data type when creating my temp table. The problem was occurring due to the data type being varchar so I changed it to float and then I was able to summarize the data in the columns.
Not sure if I had mentioned what I was using this SQL query for.
I will be using to gather data into a temp table and displaying the results in a report using Crystal Reports XI.
Thanks for all of your help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply