summing values in array

[mrscoop]

Hello,

Am working on a custom joomla component....

 

I want to sum db column TICS where column TACS equals 0 and add it to sum column TACS

The query needs to be grouped by a separate column name TOE.

 

TICS           TACS

5                    0

5                    5

1                    0

3                    3

 

so the answer should be

(5 + 1) + (5+ 3)=14

 

I stumble over arrays and formating queries to make certain types of arrays.  I figure 2 queries would be silly.

 

If anyone could point me in the right direction regarding this query I'd be much obliged. Thanks so much for taking the time to read my question!

Edited March 10, 2014 by mrscoop

[requinix]

1. "Queries"? Is this happening in a database?

2. Of the TACS that aren't zero, I assume they aren't the same values as in the TICS column?

[mrscoop]

Hi requinix,

 

Yes, I am querying a MySQL database.

 

And, yes, I posted bad example data.  The TIC and TAC columns will not necessarily be the same values when TACS is zero.

 

Thanks for having a look!

[mrscoop]

Sorry, too late to edit so here is a revision

 

TICS           TACS     TOE

5                    0          a

3                    5          c

1                    0          d

9                    3          c

 

should come to 14:

 

(5 + 1) + (5+ 3)=14

[requinix]

You can do the TICS/TACS logic in SQL quite easily with, say, the IF function.

SELECT SUM(IF(TACS = 0, TICS, TACS)) AS sum FROM table...

[mrscoop]

Thanks for the information, it has definitely headed me in the right direction:

I expected the sum of TACS to be 9 as the query is grouped by TOE
But the sum is 18

$query = $this->_db->getQuery ( true );
$query->select( 'sum(if(tacs=0, tics, tacs))');
$query->from ( 'table ');
$query->group('toe');
$this->_db->setQuery ( $query );
$testsums = $this->_db->loadRowList();

 

returns this array:  array {10,18,12)

Here is a new table with my actual values
TICS        TACS        TOE

6                 9          a
12               0          c
10               0          d
4                 9          a

Expected results: 9 (grouped tacs) 10 + 12  (tics where tacs is zero)

 

I appreciate your help.  I am inspired to really learn instead of just getting by!

[Psycho]

I expected the sum of TACS to be 9 as the query is grouped by TOE

 

Um, no. You are grouping the two records

 

TICS        TACS        TOE

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

6                 9          a

4                 9          a

 

And, in the query you using a GROUP BY and a SUM() function. The SUM() function will SUM the grouped records. 9 + 9 = 18.

[mrscoop]

Thanks Psycho, Now you further see why I need help!  How do I accomplish the goal? 

[Psycho]

Thanks Psycho, Now you further see why I need help!  How do I accomplish the goal? 

 

I have no clue, since you were supplied a solution for what you asked. You keep changing the problem, just look at the sample data you posted in your first post, third post and fifth post. In this last go around you state you expect the TACS result for the records with a TOE of a to be '9' when you do a GROUP BY. Well, your last example data had:

 

TICS        TACS        TOE

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

6                 9          a

4                 9          a

 

So, both records have a 9. So, there is no way for us to have any clue as to what logic to provide you. What if they were different? Do you want the highest value, the lowest, an average, or what? If is very frustrating to provide free help to people to provide a solution only to have them change the requirements. So, have some consideration and clearly explain what it is you are trying to achieve. If this TIC TAC TOE nonsense is just that, then explain what you are really doing as it may add some context to help us understand better.

[mrscoop]

In my original question I assumed that there would be a single query and I would work with the array. Because I am a beginner!  When I followed requnix's advice I did not get the desired results.  So I posted new data.

 

If my question irritates you you do not need to respond. It is not helpful!

[mrscoop]

So's y'all know, this chart:

 

TICS        TACS        TOE

6                 9          a
12               0          c
10               0          d
4                 9          a

 

is actual, literal values in my db.  TOE is an encrypted id that is about 80 characters long.

I want to sum db column TICS where column TACS equals 0 (12 + 10)

I want to sum unique values of TACS / or group by TOE (9)

I want to add the 2 numbers together. (equals 31)

Because I am a beginner, I do not know the best approach.  If anyone can help (and play nice) I would be grateful.

Edited March 11, 2014 by mrscoop

[mrscoop]

Could not edit in time to add this, sorry for the disjointed post.

 

Matching TOES will have the same value in TACS.

 

Sorry if I did not word that properly...I am a beginner ( but you probably already knew that).