Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Query help Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 7:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 5:56 AM
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.
Post #1433766
Posted Thursday, March 21, 2013 7:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:44 AM
Points: 12,910, Visits: 32,015
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1433771
Posted Thursday, March 21, 2013 7:24 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 23,299, Visits: 32,046
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



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)
Post #1433773
Posted Thursday, March 21, 2013 7:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 5:56 AM
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.
Post #1433787
Posted Friday, March 22, 2013 1:09 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:13 AM
Points: 2,631, Visits: 4,723
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/
Post #1434490
Posted Monday, March 25, 2013 8:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 5:56 AM
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.
Post #1434954
Posted Monday, March 25, 2013 11:13 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:13 AM
Points: 2,631, Visits: 4,723
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/
Post #1435258
Posted Tuesday, March 26, 2013 5:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 5:56 AM
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.
Post #1435411
Posted Wednesday, March 27, 2013 3:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:13 AM
Points: 2,631, Visits: 4,723
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/
Post #1435801
Posted Wednesday, March 27, 2013 6:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 5:56 AM
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.
Post #1435852
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse