473,322 Members | 1,409 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Multiple Queries from the same table ordered by user not working

Hey,

So i'm relatively new to using SQL, or at least haven't used it since my college days and therefor can't seem to get something pretty basic to work.

I have ONE table called TRACKING_TRAIN that tracks all activities from one user. Each activity is assigned a code. The table captures the transaction code, the time it was done, and the username of who did the transaction.

I'm trying to pull bits and pieces of this information into 1 table as a means of measuring someones productivity by the number of actions on a certain day.

Example of what I want my table to look like

Name | Action 1 | Action 2 | Action 3 |
------------------------------------------
John | 34 | 2 | 4 |
Sarah | 2 | 2 | 3 |


Because all actions are a different I'm using 3 different queries. The first pulls the user_ID and counts the number of items that were touched using Action 1, grouping it by User_ID. That part works fine. Once I add my additional queries I don't really know how to merge them so I get duplicate results of the name field.

Here's an example of my code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT User_ID, count(DISTINCT object_Id) as Action 1, null as Action 2, null as Action 3
  3. FROM TABLE 1
  4. WHERE tran_type = '010'
  5. AND tran_code = '004'
  6. AND TRUNC(MOD_DATE_TIME) = TRUNC(SYSDATE -6)
  7. Group by user_id
  8. UNION
  9. SELECT User_ID, null as Action 1, count(DISTINCT object_Id) as Action 2, null as Action 3
  10. FROM TABLE 1
  11. WHERE tran_type = '500'
  12. AND tran_code = '003'
  13. AND TRUNC(MOD_DATE_TIME) = TRUNC(SYSDATE -6)
  14. Group by user_id
  15. UNION
  16. SELECT User_ID, null as Action 1, null as Action 2, count(DISTINCT object_Id) as Action 3
  17. FROM TABLE 1
  18. WHERE tran_type = '100'
  19. AND tran_code = '090'
  20. AND TRUNC(MOD_DATE_TIME) = TRUNC(SYSDATE -6)
  21. Group by user_id
  22.  
  23.  
I'm probably making a pretty dumb mistake, but it's been years since college and I never really paid attention in my SQL class. Be gentle.
Feb 8 '12 #1
11 1650
Rabbit
12,516 Expert Mod 8TB
You should union them all and then do the count. Not count each separately and then union them.
Feb 8 '12 #2
But they're all from the same table and going to the same table. The action id numbers all come from the same column. Does that make sense? I'm missing something pretty obvious somewhere.
Feb 8 '12 #3
Rabbit
12,516 Expert Mod 8TB
I'm just confused now. It would help to see sample input data and resulting output data.
Feb 8 '12 #4
I'll try. The table i'm getting info from will look something like this:



User_ID.|.Transaction_Type.|.Transaction_Code.|... Date...|
----------------------------------------------------------
ROBERT..|........800.......|.......004........|201 1-12-29|
GOLLUM..|........700.......|.......009........|201 1-12-29|
GOLLUM..|........500.......|.......001........|201 1-12-28|
GOLLUM..|........500.......|.......001........|201 1-12-29|
ROBERT..|........800.......|.......004........|201 1-12-29|
GOLLUM..|........500.......|.......001........|201 1-12-29|
GOLLUM..|........800.......|.......004........|201 1-12-29|
GOLLUM..|........700.......|.......009........|201 1-12-29|


The Transaction_Type and Transaction_Code when combined end up being the equivalent of an action. Like an order being placed, picked, shipped, etc.

So what I want from the table above, is a breakdown of actions from a person on a given day. (as an example, the 29th of december)

Something like this:

Username |.800.004.|.700.009.|.500.001.| <--- numbers = action taken
----------------------------------------
ROBERT...|....2....|....0....|....0....|
GOLLUM...|....1....|....2....|....2....|


I'm hoping that makes more sense.
Feb 8 '12 #5
Rabbit
12,516 Expert Mod 8TB
You're looking for a pivot query but that's going to be unyieldy depending on how many combinations of transaction type and code you have.
Feb 9 '12 #6
I was concerned about having to do a subquery for every group in the main query, but your solution seems to have sidestepped this. It looks like it would only be the two subqueries regardless of how many groups there were. I think that should give you what you are looking for. Use the second query as your row source, then set the first column (UserRow) width to zero. I built a table that looks just like yours (at least what you described below), and then built these two queries. They came out looking precisely the way you asked for it.

Thanks,
Jackie
Feb 9 '12 #7
@Rabbit

I'm only looking for 3 combinations.

Transaction_Type | Transaction Code
-----------------------------------
.......800.......|.........004.........
.......800.......|.........009.........
.......998.......|.........001.........
Feb 9 '12 #8
@JackieBolinsky
I had already tried doing subqueries in my initial select statement but the problem I got was that it returns the total number across all users and then slaps it under every single username. It might be that I put my subqueries in the wrong spot but I honestly don't see where else/how I can put them to get the desired result.

I forgot how rigid SQL was and suddenly remember why I stopped pursuing business programming/database management stuffs in college.
Feb 9 '12 #9
Rabbit
12,516 Expert Mod 8TB
Oh, just 3? Then definitely use the pivot.
Feb 9 '12 #10
ck9663
2,878 Expert 2GB
Now that you mentioned that you're looking for specific actions, that will make your life a little easier.

Try this

Expand|Select|Wrap|Line Numbers
  1.  
  2. with YourTransactionTable
  3. as
  4. (select cast(null as varchar(10)) as UserID,cast(null as smallint) as Transaction_Type, cast('' as varchar(5)) as Transaction_Code,cast(null as Datetime) as trandate
  5. union all
  6. select 'ROBERT','800','004','2011-12-29'
  7. union all
  8. select 'GOLLUM','700','009','2011-12-29'
  9. union all
  10. select 'GOLLUM','500','001','2011-12-28'
  11. union all
  12. select 'GOLLUM','500','001','2011-12-29'
  13. union all
  14. select 'ROBERT','800','004','2011-12-29'
  15. union all
  16. select 'GOLLUM','500','001','2011-12-29'
  17. union all
  18. select 'GOLLUM','800','004','2011-12-29'
  19. union all
  20. select 'GOLLUM','700','009','2011-12-29'
  21. )
  22. select userid, Act_800_004, Act_800_009, Act_998_001
  23. from 
  24.    (
  25.       select 
  26.          userid, ActionTaken = 'Act_' + cast(Transaction_Type as varchar(5)) + '_' + Transaction_Code
  27.       from YourTransactionTable
  28.       where UserId is not null 
  29.       and 'Act_' + cast(Transaction_Type as varchar(5)) + '_' + Transaction_Code in ('Act_800_004','Act_800_009','Act_998_001')   
  30.    ) as P
  31. PIVOT
  32.    (
  33.       count(ActionTaken) for ActionTaken in ([Act_800_004],[Act_800_009],[Act_998_001])   
  34.    ) as YourRequestedOutput;   
  35.  
Forget about the CTE, that's just to have a sample data to present to you.

Happy Coding!!!


~~ CK
Feb 9 '12 #11
I actually got it to work the way I wanted to today using nested CASE statements with a SUM function. I'll post it tomorrow when I get access to the computer that I typed it up on again. There's a bit of fine tuning needed for little bits and pieces, but the core of it seems to work and doesn't take a huge amount of time to process.

Thanks for trying to help me get back on my coding horse.
Feb 9 '12 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: caro | last post by:
Hi I am trying to write two Select * statements to the same text file using bcp (from a stored procedure). But cannot find a way of appending to a file using bcp. Does anyone know if this...
4
by: ctoth | last post by:
I'm writing an ASP page for a project and it requires multiple queries. However, I'm trying to combine multiple SELECT statements, but can't figure out a way that actually works. Basically, here...
3
by: Gord | last post by:
I would like to create a summary report from the results of 11 queries (based on 2 tables). All the queries have the same format and return 3 numbers (Count, Current Year Bal, Last Year Bal.)...
4
by: Dave Edwards | last post by:
I understand that I can fill a datagrid with multiple queries, but I cannot figure out how to fill a dataset with the same query but run against multiple SQL servers, the query , table structure...
8
by: beretta819 | last post by:
Ok, so I apologize in advance for the wordiness of what follows... (I am not looking for someone to make this for me, but to point me in the right direction for the steps I need to take.) I was...
7
by: vaiism | last post by:
I am creating a report that outputs the contact information and details about a water treatment plant, and needs to include information about people who work there. If I tie all the information...
1
by: showmegeek | last post by:
I have one main table and multiple queries that give back information from different distribution list, what I would like to do is create one report that is used genericly to give me the information...
4
by: Akhenaten | last post by:
I am currently using enterprise manager to run multiple queries on a single table in a DB. I refresh these queries every few minutes. Due to the huge number of them I was looking for a better way...
2
by: DigiLife | last post by:
Greetings all, I am trying to create a form in access 2007 that will return multiple queries (count). I have an appointment table and based off of the date selection I want a grid similar to "Cheap...
1
by: dougancil | last post by:
I have multiple queries that I need to run when a user clicks a button but they are all separate queries. Can this be done?
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.