Welcome Guest, Not a member yet?
 Need help with data arrays RobertSFMember Posts: 239 Threads: 7 Joined: Oct 2014 Reputation: 03-16-2015, 12:34 PM (This post was last modified: 03-16-2015, 12:35 PM by RobertSF.) (03-16-2015, 04:46 AM)blackbulldog Wrote: Sorry to be a pain . . .  If the children table had a toytype filed then I need to separately SUM children.toys where toytype =1 and another SUM for where toytype = 2 How can I do that? Is it possible in one query? Hey, no problem. The forum is to ask questions. Anyway, yes, you can do that with one query. Suppose you have tables like this. Code:```Table: parents id  name 1  Smith, Joe 2  Doe, Mary 3  Chang, Tony 4  Martinez, R 5  Kramer, Helen 6  Green, Robert Table: children id  parents  name     toytype  toycount 1     1     Kimmie      1        3 2     1     Kimmie      2        4 3     1     Josh        1        1 4     1     Josh        2        7 5     1     Betty       1        3 6     2     Jacob       1        4 7     2     Jacob       1        1 8     2     Marnie      2       17 9     2     Taylor      1        8 10     2     Taylor      2        3 11     3     Martin      1        3 12     3     Martin      2        8 13     3     Tony        1        0 14     3     Tony        2        0 15     4     Hailey      1       13 16     6     Barbara     1        3 17     6     Freddie     1        1 18     6     Freddie     2        2 19     6     Suzy        1        7 20     6     Sophie      2        8``` Here's the query. Code:```SELECT   parents.id AS id,   parents.name AS name,   children.toytype AS ttype,   SUM(children.toycount) AS numtoys FROM parents INNER JOIN children ON parents.id = children.parents GROUP BY ttype, id ORDER BY id, ttype```The key is the GROUP BY clause. It affects how the SUM() is done. If you just grouped by ttype, you'd get two lines, one with the grand total of all type 1 toys, and the other with the grand total of all type 2 toys. If you just grouped by id (the parent's id), you'd get a line for each parent with the total sum of toys of both types. When you group it first by ttype and then by id, however, you get this. Code:```id  name           ttype  numtoys 1  Smith, Joe       1       7 1  Smith, Joe       2      11 2  Doe, Mary        1      13 2  Doe, Mary        2      20 3  Chang, Tony      1       3 3  Chang, Tony      2       8 4  Martinez, R      1      13 6  Green, Robert    1      11 6  Green, Robert    2      10```The ORDER BY clause sorts the results to make sure the rows are sorted by parent id. You could also order by parents.name or any other field. What is your MySQL setup? I really suggest you get phpMyAdmin, which lets you test different queries. There's an actual free desktop application out there called MySQL Admin, but it is very sluggish, and I found it hard to understand. Consider also getting an SQL book. I like "Simply SQL," by Rudy Limeback. It's \$9.99 on Kindle, and you can also get it used. The book really helped me understand joins. Hey, don't work without a PHP debugger. Several free IDEs have this features built in. Two are NetBeans and CodeLobster. Without a debugger, it's like you're driving with a blindfold on -- you are going to crash! .pborder { border-top: 1px dashed #ccc; padding-bottom: 10px; margin: 15px; }

 Messages In This Thread Need help with data arrays - by blackbulldog - 03-14-2015, 08:28 AM RE: Need help with data arrays - by RobertSF - 03-14-2015, 04:01 PM RE: Need help with data arrays - by blackbulldog - 03-15-2015, 05:04 AM RE: Need help with data arrays - by RobertSF - 03-15-2015, 05:42 AM RE: Need help with data arrays - by blackbulldog - 03-16-2015, 04:46 AM RE: Need help with data arrays - by RobertSF - 03-16-2015, 12:34 PM RE: Need help with data arrays - by blackbulldog - 03-16-2015, 01:27 PM RE: Need help with data arrays - by RobertSF - 03-16-2015, 05:36 PM RE: Need help with data arrays - by RobertSF - 03-16-2015, 03:24 PM RE: Need help with data arrays - by blackbulldog - 03-17-2015, 01:57 AM RE: Need help with data arrays - by RobertSF - 03-17-2015, 03:21 AM