Need help with grouping and sum in array

[cmckenna]

Hello all,

 

Need a little help with grouping and summing using an array. I can't do this directly from the database because the vendor package we are using (Tivoli Data Warehouse) stores date in a proprietary format as a CHAR in the database and in GMT so I have to get the data first and then manipulate the date to get it human readable and in the right EST date.

 

I have my query (and my date conversion) from the database returning the data in a format that looks like this:

 

The reason each date has the service repeated is because it was selected from the database by hour (needed to convert Tivoli's weird timestamp and use of GMT)

 

Service TX_Count Date

-------------------------------------------------------------------------------------------

Service1 23451 2010-01-01

Service1 93874 2010-01-01

Service1 82363 2010-01-01

Service1 56245 2010-01-02

Service1 73453 2010-01-02

Service1 18965 2010-01-02

 

I have successfully gotten the data in to an array and I can group by date and then sum the tx_count, or I can group the services and sum the tx_count.

 

What I really need to be able to do is to get the transaction counts by service by day so that I would get something like this as a result

 

Service1 2010-01-01 199688

Service1 2010-01-02 148663

 

So I need to group by service and then date and then sum it all out of the array.

 

Any advice and direction is appreciated

[PFMaBiSmAd]

SELECT SUM(tx_count) as total FROM your_table WHERE your_where_condition_here GROUP BY service,date

[cmckenna]

I can't do this via a select from the database because the date is stored not as a timestamp but as a proprietary format as CHAR.  In the post I explained that I have to get the data from the database first, manipulate the dates to get them from the proprietary format and GMT to regular dates and EST.

 

After I do all this I am left with the array that has the service name, count and date so I need to do this grouping and summing from the array, not the database.

[PFMaBiSmAd]

get them from the proprietary format and GMT to regular dates and EST.

 

You can do that in the query as well...............

[cmckenna]

I would be curious as to how I can do that in the query because all of the documentation that I have read for the Tivoli Data Warehouse states that the standard SQL date operations will NOT work because it is not a timestamp.  It is just a madeup text entry that Tivoli does.  It is stored as CHAR and looks like this in the database: 1100803040000000

 

So that is why I have to get it out, convert it myself and create a date that looks like 2010-08-03

[cmckenna]

The rub that I have come up against on the date from the database is since it is in GMT if I group by date then I get 2 groups since the difference between GMT and EDT is 4 hours right now (5 when EST) so my query has to say date between 1100803040000000 and 1100804040000000.  this would give me 2 groups, one for 8/3 and one for 8/4 sine I have to do 4:00 AM to 4:00 AM when it should really all be 8/3

[PFMaBiSmAd]

Ummm. Posting the conversion algorithm or a link to the definition would certainly help someone help you.

[PFMaBiSmAd]

Using available information, the following will convert your values into a DATETIME value -

 

SELECT CONVERT(CONCAT('20',SUBSTR('1100803040000000',2,12)),DATETIME);

 

Just the DATE part would be -

 

SELECT CONVERT(CONCAT('20',SUBSTR('1100803040000000',2,6)),DATE);

 

You can than use the resulting DATE or DATETIME values in the rest of the query.

 

You should actually perform the conversion when the values are inserted so that you can operate on native DATE or DATETIME values. The queries will execute much faster.

[cmckenna]

PFMaBiSmAd,

 

I appreciate all the help but I am at a point where I know I can't do this with the select statement from the database and already have the array created.  And I have no control over the insert in to the database as it is done by IBM Tivoli and not any of our applications so I can't change the way dates go in there. 

 

So with that in mind, let's assume there is no database involved and I just want to group by and sum the array to get from this:

 

Service TX_Count Date

-------------------------------------------------------------------------------------------

Service1 23451 2010-01-01

Service1 93874 2010-01-01

Service1 82363 2010-01-01

Service1 56245 2010-01-02

Service1 73453 2010-01-02

Service1 18965 2010-01-02

 

to this:

 

Service1 2010-01-01 199688

Service1 2010-01-02 148663

 

 

So basically, like a SQL select but using the array to get the equivilant of:

Select SUM(TX_Count)

Group by Service, Date

 

I really want to do this from the array but not sure how to group more than one "column"

 

[PFMaBiSmAd]

Someone just posted how you could produce a DATE or DATETIME value in your query from the existing values that would allow you to do this all in one query.

 

Since you have not shown your array structure (all you have shown is three columns of data with their heading) no one has bothered to attempt to show you any code to process that array.

[cmckenna]

Here is the array:

 

Array (

[0] => Array ( [service] => ServiceName1 [txcount] => 2 [estdate] => 07-19-2010 )

[1] => Array ( [service] => ServiceName2 [txcount] => 25 [estdate] => 07-19-2010 )

[2] => Array ( [service] => ServiceName3 [txcount] => 6 [estdate] => 07-19-2010 )

[3] => Array ( [service] => ServiceName3 [txcount] => 2545 [estdate] => 07-19-2010 )

[4] => Array ( [service] => ServiceName2 [txcount] => 269 [estdate] => 07-19-2010 )

[5] => Array ( [service] => ServiceName3 [txcount] => 2532 [estdate] => 07-19-2010 )

[6] => Array ( [service] => ServiceName1 [txcount] => 2 [estdate] => 07-19-2010 )

[7] => Array ( [service] => ServiceName2 [txcount] => 18 [estdate] => 07-19-2010 )

[8] => Array ( [service] => ServiceName3 [txcount] => 2 [estdate] => 07-19-2010 )

[9] => Array ( [service] => ServiceName3 [txcount] => 2533 [estdate] => 07-19-2010 )

[10] => Array ( [service] => ServiceName2 [txcount] => 58 [estdate] => 07-19-2010 )

[11] => Array ( [service] => ServiceName3 [txcount] => 2330 [estdate] => 07-19-2010 )

[12] => Array ( [service] => ServiceName2 [txcount] => 18 [estdate] => 07-19-2010 )

[13] => Array ( [service] => ServiceName3 [txcount] => 1 [estdate] => 07-19-2010 )

[14] => Array ( [service] => ServiceName3 [txcount] => 1247 [estdate] => 07-19-2010 )

[15] => Array ( [service] => ServiceName2 [txcount] => 15 [estdate] => 07-19-2010 )

[16] => Array ( [service] => ServiceName4 [txcount] => 438 [estdate] => 07-19-2010 )

[17] => Array ( [service] => ServiceName3 [txcount] => 391 [estdate] => 07-19-2010 )

[18] => Array ( [service] => ServiceName2 [txcount] => 43 [estdate] => 07-19-2010 )

[19] => Array ( [service] => ServiceName4 [txcount] => 33 [estdate] => 07-19-2010 )

[20] => Array ( [service] => ServiceName3 [txcount] => 2 [estdate] => 07-19-2010 )

[21] => Array ( [service] => ServiceName3 [txcount] => 920 [estdate] => 07-19-2010 )

[22] => Array ( [service] => ServiceName2 [txcount] => 62 [estdate] => 07-19-2010 )

[23] => Array ( [service] => ServiceName4 [txcount] => 38 [estdate] => 07-19-2010 )

[24] => Array ( [service] => ServiceName3 [txcount] => 7 [estdate] => 07-19-2010 )

[25] => Array ( [service] => ServiceName3 [txcount] => 2420 [estdate] => 07-19-2010 )

[26] => Array ( [service] => ServiceName3 [txcount] => 2 [estdate] => 07-20-2010 )

[27] => Array ( [service] => ServiceName3 [txcount] => 3093 [estdate] => 07-20-2010 )

[28] => Array ( [service] => ServiceName1 [txcount] => 8 [estdate] => 07-20-2010 )

[29] => Array ( [service] => ServiceName2 [txcount] => 82 [estdate] => 07-20-2010 )

[30] => Array ( [service] => ServiceName3 [txcount] => 4 [estdate] => 07-20-2010 )

[31] => Array ( [service] => ServiceName3 [txcount] => 2505 [estdate] => 07-20-2010 )

[32] => Array ( [service] => ServiceName1 [txcount] => 7 [estdate] => 07-20-2010 )

[33] => Array ( [service] => ServiceName2 [txcount] => 30 [estdate] => 07-20-2010 )

[34] => Array ( [service] => ServiceName3 [txcount] => 2 [estdate] => 07-20-2010 )

[35] => Array ( [service] => ServiceName3 [txcount] => 2274 [estdate] => 07-20-2010 )

[36] => Array ( [service] => ServiceName1 [txcount] => 7 [estdate] => 07-20-2010 )

[37] => Array ( [service] => ServiceName2 [txcount] => 40 [estdate] => 07-20-2010 ))

[sasa]

ttry

<?php
/* 
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
$test = Array (
'0' => Array ( 'service' => 'ServiceNname1', 'txcount' => 2, 'estdate' => '07-19-2010' ),
'1' => Array ('service' => 'ServiceNname2', 'txcount' => 25, 'estdate' => '07-19-2010' ),
'2' => Array ( 'service' => 'ServiceNname3', 'txcount' => 6, 'estdate' => '07-19-2010' ),
'3' => Array ( 'service' => 'ServiceNname3', 'txcount' => 2545, 'estdate' => '07-19-2010' ),
'4' => Array ( 'service' => 'ServiceNname2', 'txcount' => 269, 'estdate' => '07-19-2010' ),
'5' => Array ( 'service' => 'ServiceNname3', 'txcount' => 2532, 'estdate' => '07-19-2010' ),
'6' => Array ( 'service' => 'ServiceNname1', 'txcount' => 2, 'estdate' => '07-19-2010' ),
'7' => Array ( 'service' => 'ServiceNname2', 'txcount' => 18, 'estdate' => '07-19-2010' ),
'8' => Array ( 'service' => 'ServiceNname3', 'txcount' => 2, 'estdate' => '07-19-2010' ),
'9' => Array ( 'service' => 'ServiceNname3', 'txcount' => 2533, 'estdate' => '07-19-2010' ),
'10' => Array ( 'service' => 'ServiceNname2', 'txcount' => 58, 'estdate' => '07-19-2010' ),
'11' => Array ( 'service' => 'ServiceNname3', 'txcount' => 2330, 'estdate' => '07-19-2010' ),
'12' => Array ( 'service' => 'ServiceNname2', 'txcount' => 18, 'estdate' => '07-19-2010' ),
'13' => Array ( 'service' => 'ServiceNname3', 'txcount' => 1, 'estdate' => '07-19-2010' ),
'14' => Array ( 'service' => 'ServiceNname3', 'txcount' => 1247, 'estdate' => '07-19-2010' ),
'15' => Array ( 'service' => 'ServiceNname2', 'txcount' => 15, 'estdate' => '07-19-2010' ),
'16' => Array ( 'service' => 'ServiceNname4', 'txcount' => 438, 'estdate' => '07-19-2010' ),
'17' => Array ( 'service' => 'ServiceNname3', 'txcount' => 391, 'estdate' => '07-19-2010' ),
'18' => Array ( 'service' => 'ServiceNname2', 'txcount' => 43, 'estdate' => '07-19-2010' ),
'19' => Array ( 'service' => 'ServiceNname4', 'txcount' => 33, 'estdate' => '07-19-2010' ),
'20' => Array ( 'service' => 'ServiceNname3', 'txcount' => 2, 'estdate' => '07-19-2010' ),
'21' => Array ( 'service' => 'ServiceNname3', 'txcount' => 920, 'estdate' => '07-19-2010' ),
'22' => Array ( 'service' => 'ServiceNname2', 'txcount' => 62, 'estdate' => '07-19-2010' ),
'23' => Array ( 'service' => 'ServiceNname4', 'txcount' => 38, 'estdate' => '07-19-2010' ),
'24' => Array ( 'service' => 'ServiceNname3', 'txcount' => 7, 'estdate' => '07-19-2010' ),
'25' => Array ( 'service' => 'ServiceNname3', 'txcount' => 2420, 'estdate' => '07-19-2010' ),
'26' => Array ( 'service' => 'ServiceNname3', 'txcount' => 2, 'estdate' => '07-20-2010' ),
'27' => Array ( 'service' => 'ServiceNname3', 'txcount' => 3093, 'estdate' => '07-20-2010' ),
'28' => Array ( 'service' => 'ServiceNname1', 'txcount' => 8, 'estdate' => '07-20-2010' ),
'29' => Array ( 'service' => 'ServiceNname2', 'txcount' => 82, 'estdate' => '07-20-2010' ),
'30' => Array ( 'service' => 'ServiceNname3', 'txcount' => 4, 'estdate' => '07-20-2010' ),
'31' => Array ( 'service' => 'ServiceNname3', 'txcount' => 2505, 'estdate' => '07-20-2010' ),
'32' => Array ( 'service' => 'ServiceNname1', 'txcount' => 7, 'estdate' => '07-20-2010' ),
'33' => Array ( 'service' => 'ServiceNname2', 'txcount' => 30, 'estdate' => '07-20-2010' ),
'34' => Array ( 'service' => 'ServiceNname3', 'txcount' => 2, 'estdate' => '07-20-2010' ),
'35' => Array ( 'service' => 'ServiceNname3', 'txcount' => 2274, 'estdate' => '07-20-2010' ),
'36' => Array ( 'service' => 'ServiceNname1', 'txcount' => 7, 'estdate' => '07-20-2010' ),
'37' => Array ( 'service' => 'ServiceNname2', 'txcount' => 40, 'estdate' => '07-20- 2010' ));
$out = array();
foreach ($test as $row){
    $s = $row['service'];
    $d = $row['estdate'];
    $t = $row['txcount'];
    if(isset ($out[$s][$d])) $out[$s][$d] += $t; else $out[$s][$d] = $t;
}
foreach ($out as $service => $row){
    foreach ($row as $date => $cnt) echo "$service - $date - $cnt<br />\n";
}
?>

[cmckenna]

THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU!

 

This worked perfectly and after reviewing the code I actually understand it.

 

YOU ARE MY HERO!!!!!