Need help using Group BY with data from an OLE DB connection

[neena123]

Hello! I need some help. Never worked with an OLE DB source before and I am having trouble understanding how to do a Group By. I am trying to calculate how many packages is shipped per user by hour. I am trying to group the fields Username and Date so that it is unique and I can then count how many packages were shipped by hour. I tried to pull the data in one table but it did not like that. I tried to do a join then a resident table and use Group By but it didn't like that either. Is it because the data is from two different Data sources? 

//Ship Logs***************************************************************************************************************************
OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=connectship;Data Source=THM-COM-r01sm;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=BRB-TPM-SMRT01;Use Encryption for Data=False;Tag with column collation when possible=False] //************************************************************************************************************************************
Ship:
LOAD
Date_Time AS Date,
Time#(right(Date_Time,12),'HH:MM:SS') AS Time,
Mid(Date_Time,10,2) AS DateHour,
Invoice_Number AS Invoice_Number
;
SQL SELECT "Date_Time","Invoice_Number"
FROM "CSW_Logs".dbo."Ship_Log";

//Packages****************************************************************************************************************************
OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=connectship;Data Source=THM-COM-r01sm;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID= BRB-TPM-SMRT01;Use Encryption for Data=False;Tag with column collation when possible=False] //************************************************************************************************************************************
LEFT JOIN
LOAD
reference_1 as Invoice_Number,
reference_10 as Username,
reference_2 as Client,
user as Facility;
SQL SELECT "reference_1","reference_10","reference_2","user"
FROM connectship.dbo.packages;

[rwunderlich]

Is there only 1 Ship per Invoice Number?  Or is there sometimes multiple Ship rows per Invoice Number?

-Rob

[neena123]

Hello! There is only 1 ship per invoice 

[rwunderlich · Accepted solution]

It seems to me that the left join you posted should work as the first step.  BTW, it's a good idea to specify the target table in the join like this:

LEFT JOIN (Ship)

Then you should be able to create a new table from the Resident Ship.

ShipCount:
LOAD count(Invoice_Number) as ShipCount,
username,
DateHour
Resident Ship
Group by username, DateHour;

-Rob