SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Query help


SQL Query help

Author
Message
jrseven68
jrseven68
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 34
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.
Lowell
Lowell
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37151 Visits: 40280
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52668 Visits: 38684
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

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
jrseven68
jrseven68
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 34
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.
Kingston Dhasian
Kingston Dhasian
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4253 Visits: 5244
This is probably what you need

SELECT   U.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
FROM users AS U
INNER JOIN rxproductivity AS P ON U.userid = P.whoverified OR U.userid = P.whochecked
GROUP BY U.userid




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
jrseven68
jrseven68
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 34
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.:-)
Kingston Dhasian
Kingston Dhasian
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4253 Visits: 5244
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.


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
jrseven68
jrseven68
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 34
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.
Kingston Dhasian
Kingston Dhasian
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4253 Visits: 5244
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


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
jrseven68
jrseven68
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 34
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search