# Join and group

**URL:** <https://forum.inductiveautomation.com/t/join-and-group/6880>\
**Category:** Ignition\
**Created:** [July 17, 2013, 8:24pm UTC](https://forum.inductiveautomation.com/t/join-and-group/6880 "2013-07-17T20:24:46Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![AJBerera](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@AJBerera](https://forum.inductiveautomation.com/u/AJBerera)\
**Post date:** [July 17, 2013, 8:24pm UTC](https://forum.inductiveautomation.com/t/join-and-group/6880/1 "2013-07-17T20:24:46Z")

</div>

I have 2 tables that both have their own time stamps but I want to join them and look at the hourly sums of the columns and group them accordindly.

This is the query I have now:

`SELECT SUM(t.Oubound90Jam) AS Outbound90Jam, SUM(t.OuboundMergeJam) AS "OutboundMergeJam",SUM(t.OuboundCurveAJam) AS "OutboundCurveAJam",SUM(t.OuboundCurveBJam) AS "OutboundCurveBJam", SUM(t.OuboundEStops) AS "OutboundEStops", 
CAST(DATEPART(month, t.t_stamp) AS varchar) + '-' + CAST(DATEPART(day, t.t_stamp) AS varchar) + ' ' + RIGHT('0' + CAST(DATEPART(hour, t.t_stamp) AS varchar), 2) + ':00' AS Hour, 
SUM(p.OutboundWest180CurveJams) AS "OutboundWest180CurveJams", SUM(p.OutboundEast180CurveJams) AS "OutboundEast180CurveJams", SUM(p.OutboundMDR90CurveJams) AS "OutboundMDR90CurveJams", 
SUM(p.OutboundMergeJams) AS "OutboundMergeJams",CAST(DATEPART(month, t.t_stamp) AS varchar) + '-' + CAST(DATEPART(day, t.t_stamp) AS varchar) + ' ' + RIGHT('0' + CAST(DATEPART(hour, t.t_stamp) AS varchar), 2) + ':00' AS Expr1
FROM dbo.OutboundDowntime AS t CROSS JOIN
dbo.RapidPackInOutProduction AS p
WHERE t.t_stamp BETWEEN Cast('{Root Container.OutboundCartonFaults.StartDate} {Root Container.OutboundCartonFaults.Start}' as datetime) 
and Cast('{Root Container.OutboundCartonFaults.EndDate} {Root Container.OutboundCartonFaults.End}' as datetime) AND 
CAST(DATEPART(month, t.t_stamp) AS varchar) + '-' + CAST(DATEPART(day, t.t_stamp) AS varchar) + ' ' + RIGHT('0' + CAST(DATEPART(hour, t.t_stamp) AS varchar), 2) =
CAST(DATEPART(month, p.t_stamp) AS varchar) + '-' + CAST(DATEPART(day, p.t_stamp) AS varchar) + ' ' + RIGHT('0' + CAST(DATEPART(hour, p.t_stamp) AS varchar), 2)
GROUP BY CAST(DATEPART(month, t.t_stamp) AS varchar) + '-' + CAST(DATEPART(day, t.t_stamp) AS varchar) + ' ' + RIGHT('0' + CAST(DATEPART(hour, t.t_stamp) AS varchar), 2),
CAST(DATEPART(month, p.t_stamp) AS varchar) + '-' + CAST(DATEPART(day, p.t_stamp) AS varchar) + ' ' + RIGHT('0' + CAST(DATEPART(hour, p.t_stamp) AS varchar), 2)
ORDER BY Hour`

I believe the problem is the cross join. I am not producing the correct results, my values seem to be adding too many times.  
I am producing 1 row per minute in each table.

Does anyone have any suggestions or can see the problems with the code?

---

<div class="post-metadata">

**Author:** ![adamaustin](https://avatars.discourse-cdn.com/v4/letter/a/5f8ce5/32.png) [@adamaustin](https://forum.inductiveautomation.com/u/adamaustin)\
**Post date:** [July 17, 2013, 9:47pm UTC](https://forum.inductiveautomation.com/t/join-and-group/6880/2 "2013-07-17T21:47:41Z")

</div>

It looks like you are probably getting a GROUP BY problem.

I noticed that EXPR1 is the same as HOUR.

You could try changing that and see if it helps.

SELECT  
SUM(t.Oubound90Jam) AS Outbound90Jam,  
SUM(t.OuboundMergeJam) AS “OutboundMergeJam”,  
SUM(t.OuboundCurveAJam) AS “OutboundCurveAJam”,  
SUM(t.OuboundCurveBJam) AS “OutboundCurveBJam”,  
SUM(t.OuboundEStops) AS “OutboundEStops”,  
CAST(DATEPART(month, t.t\_stamp) AS varchar) + ‘-’ + CAST(DATEPART(day, t.t\_stamp) AS varchar) + ’ ’ + RIGHT(‘0’ + CAST(DATEPART(hour, t.t\_stamp) AS varchar), 2) + ‘:00’ AS Hour,   
SUM(p.OutboundWest180CurveJams) AS “OutboundWest180CurveJams”,  
SUM(p.OutboundEast180CurveJams) AS “OutboundEast180CurveJams”,  
SUM(p.OutboundMDR90CurveJams) AS “OutboundMDR90CurveJams”,  
SUM(p.OutboundMergeJams) AS “OutboundMergeJams”,  
CAST(DATEPART(month, t.t\_stamp) AS varchar) + ‘-’ + CAST(DATEPART(day, t.t\_stamp) AS varchar) + ’ ’ + RIGHT(‘0’ + CAST(DATEPART(hour, t.t\_stamp) AS varchar), 2) + ‘:00’ AS Expr1  
FROM dbo.OutboundDowntime AS t  
CROSS JOIN dbo.RapidPackInOutProduction AS p  
WHERE t.t\_stamp  
BETWEEN Cast(’{Root Container.OutboundCartonFaults.StartDate} {Root Container.OutboundCartonFaults.Start}’ as datetime)  
AND Cast(’{Root Container.OutboundCartonFaults.EndDate} {Root Container.OutboundCartonFaults.End}’ as datetime)  
AND  
CAST(DATEPART(month, t.t\_stamp) AS varchar) + ‘-’ + CAST(DATEPART(day, t.t\_stamp) AS varchar) + ’ ’ + RIGHT(‘0’ + CAST(DATEPART(hour, t.t\_stamp) AS varchar), 2)  
= CAST(DATEPART(month, p.t\_stamp) AS varchar) + ‘-’ + CAST(DATEPART(day, p.t\_stamp) AS varchar) + ’ ’ + RIGHT(‘0’ + CAST(DATEPART(hour, p.t\_stamp) AS varchar), 2)  
GROUP BY  
CAST(DATEPART(month, t.t\_stamp) AS varchar) + ‘-’ + CAST(DATEPART(day, t.t\_stamp) AS varchar) + ’ ’ + RIGHT(‘0’ + CAST(DATEPART(hour, t.t\_stamp) AS varchar), 2),  
CAST(DATEPART(month, p.t\_stamp) AS varchar) + ‘-’ + CAST(DATEPART(day, p.t\_stamp) AS varchar) + ’ ’ + RIGHT(‘0’ + CAST(DATEPART(hour, p.t\_stamp) AS varchar), 2)  
ORDER BY Hour

---

<div class="post-metadata">

**Author:** ![AJBerera](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@AJBerera](https://forum.inductiveautomation.com/u/AJBerera)\
**Post date:** [July 17, 2013, 9:56pm UTC](https://forum.inductiveautomation.com/t/join-and-group/6880/3 "2013-07-17T21:56:58Z")

</div>

I was able to get a little deeper into this;  
I believe the group By line which is supposed to control what rows I am actually supposed to sum is not correct because now I have 2 tables each with its own Summing range…  
I thought the cross join was a problem and I replaced with the INNER join and am trying to be more exact in this script but I think the group by line is definitly wrong:

[code]SELECT SUM(t.Oubound90Jam) AS Outbound90Jam, SUM(t.OuboundMergeJam) AS “OutboundMergeJam”,SUM(t.OuboundCurveAJam) AS “OutboundCurveAJam”,SUM(t.OuboundCurveBJam) AS “OutboundCurveBJam”, SUM(t.OuboundEStops) AS “OutboundEStops”,  
CAST(DATEPART(month, t.t\_stamp) AS varchar) + ‘-’ + CAST(DATEPART(day, t.t\_stamp) AS varchar) + ’ ’ + RIGHT(‘0’ + CAST(DATEPART(hour, t.t\_stamp) AS varchar), 2) + ‘:00’ AS [Hour],  
SUM(p.OutboundWest180CurveJams) AS “OutboundWest180CurveJams”, SUM(p.OutboundEast180CurveJams) AS “OutboundEast180CurveJams”, SUM(p.OutboundMDR90CurveJams) AS “OutboundMDR90CurveJams”,SUM(p.Totalestops) AS “Totalestops”,  
CAST(DATEPART(month, p.t\_stamp) AS varchar) + ‘-’ + CAST(DATEPART(day, p.t\_stamp) AS varchar) + ’ ’ + RIGHT(‘0’ + CAST(DATEPART(hour, p.t\_stamp) AS varchar), 2) + ‘:00’ as Expir1  
FROM OutboundDowntime t  
INNER JOIN RapidPackInOutProduction p  
ON CAST(DATEPART(month, t.t\_stamp) AS varchar) + ‘-’ + CAST(DATEPART(day, t.t\_stamp) AS varchar) + ’ ’ + RIGHT(‘0’ + CAST(DATEPART(hour, t.t\_stamp) AS varchar), 2) + ‘:00’ =  
CAST(DATEPART(month, p.t\_stamp) AS varchar) + ‘-’ + CAST(DATEPART(day, p.t\_stamp) AS varchar) + ’ ’ + RIGHT(‘0’ + CAST(DATEPART(hour, p.t\_stamp) AS varchar), 2) + ‘:00’

WHERE t.t\_stamp BETWEEN Cast(’{Root Container.OutboundCartonFaults.StartDate} {Root Container.OutboundCartonFaults.Start}’ as datetime)  
and Cast(’{Root Container.OutboundCartonFaults.EndDate} {Root Container.OutboundCartonFaults.End}’ as datetime)  
Group By CAST(DATEPART(month, t.t\_stamp) AS varchar) + ‘-’ + CAST(DATEPART(day, t.t\_stamp) AS varchar) + ’ ’ + RIGHT(‘0’ + CAST(DATEPART(hour, t.t\_stamp) AS varchar), 2) + ‘:00’,  
CAST(DATEPART(month, p.t\_stamp) AS varchar) + ‘-’ + CAST(DATEPART(day, p.t\_stamp) AS varchar) + ’ ’ + RIGHT(‘0’ + CAST(DATEPART(hour, p.t\_stamp) AS varchar), 2) + ‘:00’  
ORDER BY Hour  
[/code]