Welcome Guest, Not a member yet? Register   Sign In
Need help with data arrays
#6

(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!
Reply


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



Theme © iAndrew 2016 - Forum software by © MyBB