Multiple Queries from the same table ordered by user not working

[ImAStegosaurus]

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:

SELECT User_ID, count(DISTINCT object_Id) as Action 1, null as Action 2, null as Action 3
FROM TABLE 1
WHERE tran_type = '010'
AND tran_code = '004'
AND TRUNC(MOD_DATE_TIME) = TRUNC(SYSDATE -6)
Group by user_id
UNION
SELECT User_ID, null as Action 1, count(DISTINCT object_Id) as Action 2, null as Action 3
FROM TABLE 1
WHERE tran_type = '500'
AND tran_code = '003'
AND TRUNC(MOD_DATE_TIME) = TRUNC(SYSDATE -6)
Group by user_id
UNION
SELECT User_ID, null as Action 1, null as Action 2, count(DISTINCT object_Id) as Action 3
FROM TABLE 1
WHERE tran_type = '100'
AND tran_code = '090'
AND TRUNC(MOD_DATE_TIME) = TRUNC(SYSDATE -6)
Group by user_id

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.

[Rabbit]

You should union them all and then do the count. Not count each separately and then union them.

[ImAStegosaurus]

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.

[Rabbit]

I'm just confused now. It would help to see sample input data and resulting output data.

[ImAStegosaurus]

I'll try. The table i'm getting info from will look something like this:



User_ID.|.Trans action_Type.|.T ransaction_Code .|...Date...|
----------------------------------------------------------
ROBERT..|...... ..800.......|.. .....004....... .|2011-12-29|
GOLLUM..|...... ..700.......|.. .....009....... .|2011-12-29|
GOLLUM..|...... ..500.......|.. .....001....... .|2011-12-28|
GOLLUM..|...... ..500.......|.. .....001....... .|2011-12-29|
ROBERT..|...... ..800.......|.. .....004....... .|2011-12-29|
GOLLUM..|...... ..500.......|.. .....001....... .|2011-12-29|
GOLLUM..|...... ..800.......|.. .....004....... .|2011-12-29|
GOLLUM..|...... ..700.......|.. .....009....... .|2011-12-29|


The Transaction_Typ e and Transaction_Cod e 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.

[Rabbit]

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.

[JackieBolinsky]

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

[ImAStegosaurus]

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.

I'm only looking for 3 combinations.

Transaction_Typ e | Transaction Code
-----------------------------------
.......800..... ..|.........004 .........
.......800..... ..|.........009 .........
.......998..... ..|.........001 .........

[ImAStegosaurus]

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

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.

[Rabbit]

Oh, just 3? Then definitely use the pivot.

[ck9663]

Now that you mentioned that you're looking for specific actions, that will make your life a little easier.

Try this

with YourTransactionTable
as
(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
union all
select 'ROBERT','800','004','2011-12-29'
union all
select 'GOLLUM','700','009','2011-12-29'
union all
select 'GOLLUM','500','001','2011-12-28'
union all
select 'GOLLUM','500','001','2011-12-29'
union all
select 'ROBERT','800','004','2011-12-29'
union all
select 'GOLLUM','500','001','2011-12-29'
union all
select 'GOLLUM','800','004','2011-12-29'
union all
select 'GOLLUM','700','009','2011-12-29'
)
select userid, Act_800_004, Act_800_009, Act_998_001
from 
   (
      select 
         userid, ActionTaken = 'Act_' + cast(Transaction_Type as varchar(5)) + '_' + Transaction_Code
      from YourTransactionTable
      where UserId is not null 
      and 'Act_' + cast(Transaction_Type as varchar(5)) + '_' + Transaction_Code in ('Act_800_004','Act_800_009','Act_998_001')   
   ) as P
PIVOT
   (
      count(ActionTaken) for ActionTaken in ([Act_800_004],[Act_800_009],[Act_998_001])   
   ) as YourRequestedOutput;

Forget about the CTE, that's just to have a sample data to present to you.

Happy Coding!!!


~~ CK

[ImAStegosaurus]

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.