ISoft Wiki - User contributions [en]

Presage/User Guide/ITrackLX Mobile App

2025-04-09T13:54:43Z

Jwoody: /* Install the ITrack Mobile App on the iPod. */

==How to insert the iPod in the Linea Pro scanner.==

[[File:Presageattachipodtoscanner.png|thumb|center|600px]]

==Install the ITrack Mobile App on the iPod.==

Click on the '''App Store''' icon on the iPod.

Type in ''isoftdata'', and swipe from right to left until the following app shows up:

[[File:Itrackapp.jpg|thumb|center|300px]]
*''The button may look different.''

Click on the cloud button to install the app.

The following icon pops-up. Click '''Use Existing Apple ID'''.

[[File:Itrackapp1.jpg|thumb|center|300px]]

Enter the Apple ID and password. (A Presage representative will provide you with this information. It will be used across all plants in your companies, unless requested otherwise.)

[[File:Itrackapp2.jpg|thumb|center|300px]]

In the Home Screen, find the ITrackLX Mobile app icon and click on it.

[[File:Itrackapp4.jpg|thumb|center|300px]]

Enter the Server URL provided by the Presage representative.
Presage URLs:
https://COMPANY.presageanalytics.com/scanner

Most presage deployments still have the old subdomain url's as well:
COMPANY-scan.presageanalytics.com
New deployments won't have them, and the existing ones will start getting redirected to the subdirectory url's as we update them

[[File:Itrackapp3.jpg|thumb|center|300px]]

Login with Presage username and password and click '''Log in!'''.

==Perform Sample==

To time-stamp when a sample was taken, click on the first button '''Perform Sample'''.

Press the button on the right side of the Linea Pro.

Place the red ray on the barcode. Hold until a sound comes out validating that the barcode was read.

A notification will appear confirming that the sample was marked.

Click '''Add Image''' to attach an image to the sample.

Click '''Done''' to go back to the main menu.

The above steps will automatically update the work order in Presage.

1) The ''Status'' will change from ''Open'' to ''Sampled''.
2) ''Performed On'' will be filled with the scan date and time.
3) ''Performed By'' will be filled with the user who logged in to the mobile app.

==Incubate Sample==

To time-stamp when a test is going in and out of the incubator, click on the second button '''Incubate Sample'''.

Press the button on the right side of the Linea Pro.

Place the red ray on the barcode. Hold until a sound comes out validating that the barcode was read.

A notification will appear confirming that the sample was marked.

Click '''Add Image''' to attach an image to the sample.

Click '''Done''' to go back to the main menu.

The above steps will automatically update the work order in Presage.

1) ''Testing Began'' will be filled with the date and time the barcode was scanned the first time.
2) ''Testing Ended'' will be filled with the date and time the barcode was scanned the second time.
3) ''Tested By'' will be filled with the user who logged in to the mobile app.

==Record Analysis==

To enter the result for a sample, click on the third button '''Record Analysis'''.

Press the button on the right side of the Linea Pro.

Place the red ray on the barcode. Hold until it beeps and sample appears on the screen.

All the options for the sample will show up. Select or type in the results.

Click '''Done''' to save and go back to the main menu.
Click '''Cancel''' to delete changes and go back to the main menu.

[[File:PresageAddImagetomobile.jpg|center]]

The above steps will automatically update the work order in Presage.

There is a setting in the software which turn on automatic investigation creation. When an out of spec result is entered, the mobile app will prompt for an initiation of an investigation sample.

[[File:Investigationmobile.jpg|center]]

For steps on how to turn on this setting, click [[How to turn on the auto-investigation creation feature.]]

==Work Orders==

This option opens a list of all open work orders.

[[File:PresageScannerOpenworkorders.jpg|center]]

Open and edit a work order.

Click '''Work Order''' in the ''Main Menu''. If you have a work order barcode, go ahead and scan that now to open the work order. If not, click '''Menu''' >> '''Manual input: Work Order'''.

To create a new work order, click '''Menu''' >> '''New Work Order'''.

Click on the link below to watch a VIDEO TUTORIALS for the Work Order screen.

[http://www.youtube.com/watch?v=kCcdj8-yK9k/ Create a New Work Order]

[http://www.youtube.com/watch?v=-XOWb98dFRM/ Find an Existing Work Order]

==Settings==

For the laser to be on at all times (instead of having to click on the button to bring up the laser), on the mobile setting, tap on ITrack LX then turn on the ''Always On''.

[[File:Alwaysonsetting.jpg|center]]

Presage/User Guide/ITrackLX Mobile App

2024-09-06T14:12:28Z

Jwoody: /* Install the ITrack Mobile App on the iPod. */

==How to insert the iPod in the Linea Pro scanner.==

[[File:Presageattachipodtoscanner.png|thumb|center|600px]]

==Install the ITrack Mobile App on the iPod.==

Click on the '''App Store''' icon on the iPod.

Type in ''isoftdata'', and swipe from right to left until the following app shows up:

[[File:Itrackapp.jpg|thumb|center|300px]]
*''The button may look different.''

Click on the cloud button to install the app.

The following icon pops-up. Click '''Use Existing Apple ID'''.

[[File:Itrackapp1.jpg|thumb|center|300px]]

Enter the Apple ID and password. (A Presage representative will provide you with this information. It will be used across all plants in your companies, unless requested otherwise.)

[[File:Itrackapp2.jpg|thumb|center|300px]]

In the Home Screen, find the ITrackLX Mobile app icon and click on it.

[[File:Itrackapp4.jpg|thumb|center|300px]]

Enter the Server URL provided by the Presage representative.
Presage URLs:
COMPANY.presageanalytics.com/scanner

Most presage deployments still have the old subdomain url's as well:
COMPANY-scan.presageanalytics.com
New deployments won't have them, and the existing ones will start getting redirected to the subdirectory url's as we update them

[[File:Itrackapp3.jpg|thumb|center|300px]]

Login with Presage username and password and click '''Log in!'''.

==Perform Sample==

To time-stamp when a sample was taken, click on the first button '''Perform Sample'''.

Press the button on the right side of the Linea Pro.

Place the red ray on the barcode. Hold until a sound comes out validating that the barcode was read.

A notification will appear confirming that the sample was marked.

Click '''Add Image''' to attach an image to the sample.

Click '''Done''' to go back to the main menu.

The above steps will automatically update the work order in Presage.

1) The ''Status'' will change from ''Open'' to ''Sampled''.
2) ''Performed On'' will be filled with the scan date and time.
3) ''Performed By'' will be filled with the user who logged in to the mobile app.

==Incubate Sample==

To time-stamp when a test is going in and out of the incubator, click on the second button '''Incubate Sample'''.

Press the button on the right side of the Linea Pro.

Place the red ray on the barcode. Hold until a sound comes out validating that the barcode was read.

A notification will appear confirming that the sample was marked.

Click '''Add Image''' to attach an image to the sample.

Click '''Done''' to go back to the main menu.

The above steps will automatically update the work order in Presage.

1) ''Testing Began'' will be filled with the date and time the barcode was scanned the first time.
2) ''Testing Ended'' will be filled with the date and time the barcode was scanned the second time.
3) ''Tested By'' will be filled with the user who logged in to the mobile app.

==Record Analysis==

To enter the result for a sample, click on the third button '''Record Analysis'''.

Press the button on the right side of the Linea Pro.

Place the red ray on the barcode. Hold until it beeps and sample appears on the screen.

All the options for the sample will show up. Select or type in the results.

Click '''Done''' to save and go back to the main menu.
Click '''Cancel''' to delete changes and go back to the main menu.

[[File:PresageAddImagetomobile.jpg|center]]

The above steps will automatically update the work order in Presage.

There is a setting in the software which turn on automatic investigation creation. When an out of spec result is entered, the mobile app will prompt for an initiation of an investigation sample.

[[File:Investigationmobile.jpg|center]]

For steps on how to turn on this setting, click [[How to turn on the auto-investigation creation feature.]]

==Work Orders==

This option opens a list of all open work orders.

[[File:PresageScannerOpenworkorders.jpg|center]]

Open and edit a work order.

Click '''Work Order''' in the ''Main Menu''. If you have a work order barcode, go ahead and scan that now to open the work order. If not, click '''Menu''' >> '''Manual input: Work Order'''.

To create a new work order, click '''Menu''' >> '''New Work Order'''.

Click on the link below to watch a VIDEO TUTORIALS for the Work Order screen.

[http://www.youtube.com/watch?v=kCcdj8-yK9k/ Create a New Work Order]

[http://www.youtube.com/watch?v=-XOWb98dFRM/ Find an Existing Work Order]

==Settings==

For the laser to be on at all times (instead of having to click on the button to bring up the laser), on the mobile setting, tap on ITrack LX then turn on the ''Always On''.

[[File:Alwaysonsetting.jpg|center]]

Presage/User Guide/ITrackLX Mobile App

2024-09-06T14:11:13Z

Jwoody: /* Install the ITrack Mobile App on the iPod. */

==How to insert the iPod in the Linea Pro scanner.==

[[File:Presageattachipodtoscanner.png|thumb|center|600px]]

==Install the ITrack Mobile App on the iPod.==

Click on the '''App Store''' icon on the iPod.

Type in ''isoftdata'', and swipe from right to left until the following app shows up:

[[File:Itrackapp.jpg|thumb|center|300px]]
*''The button may look different.''

Click on the cloud button to install the app.

The following icon pops-up. Click '''Use Existing Apple ID'''.

[[File:Itrackapp1.jpg|thumb|center|300px]]

Enter the Apple ID and password. (A Presage representative will provide you with this information. It will be used across all plants in your companies, unless requested otherwise.)

[[File:Itrackapp2.jpg|thumb|center|300px]]

In the Home Screen, find the ITrackLX Mobile app icon and click on it.

[[File:Itrackapp4.jpg|thumb|center|300px]]

Enter the Server URL provided by the Presage representative.
Presage URLs
COMPANY.presageanalytics.com/scanner

Most presage deployments still have the old subdomain url's as well.
COMPANY-scan.presageanalytics.com
New deployments won't have them, and the existing ones will start getting redirected to the subdirectory url's as we update them

[[File:Itrackapp3.jpg|thumb|center|300px]]

Login with Presage username and password and click '''Log in!'''.

==Perform Sample==

To time-stamp when a sample was taken, click on the first button '''Perform Sample'''.

Press the button on the right side of the Linea Pro.

Place the red ray on the barcode. Hold until a sound comes out validating that the barcode was read.

A notification will appear confirming that the sample was marked.

Click '''Add Image''' to attach an image to the sample.

Click '''Done''' to go back to the main menu.

The above steps will automatically update the work order in Presage.

1) The ''Status'' will change from ''Open'' to ''Sampled''.
2) ''Performed On'' will be filled with the scan date and time.
3) ''Performed By'' will be filled with the user who logged in to the mobile app.

==Incubate Sample==

To time-stamp when a test is going in and out of the incubator, click on the second button '''Incubate Sample'''.

Press the button on the right side of the Linea Pro.

Place the red ray on the barcode. Hold until a sound comes out validating that the barcode was read.

A notification will appear confirming that the sample was marked.

Click '''Add Image''' to attach an image to the sample.

Click '''Done''' to go back to the main menu.

The above steps will automatically update the work order in Presage.

1) ''Testing Began'' will be filled with the date and time the barcode was scanned the first time.
2) ''Testing Ended'' will be filled with the date and time the barcode was scanned the second time.
3) ''Tested By'' will be filled with the user who logged in to the mobile app.

==Record Analysis==

To enter the result for a sample, click on the third button '''Record Analysis'''.

Press the button on the right side of the Linea Pro.

Place the red ray on the barcode. Hold until it beeps and sample appears on the screen.

All the options for the sample will show up. Select or type in the results.

Click '''Done''' to save and go back to the main menu.
Click '''Cancel''' to delete changes and go back to the main menu.

[[File:PresageAddImagetomobile.jpg|center]]

The above steps will automatically update the work order in Presage.

There is a setting in the software which turn on automatic investigation creation. When an out of spec result is entered, the mobile app will prompt for an initiation of an investigation sample.

[[File:Investigationmobile.jpg|center]]

For steps on how to turn on this setting, click [[How to turn on the auto-investigation creation feature.]]

==Work Orders==

This option opens a list of all open work orders.

[[File:PresageScannerOpenworkorders.jpg|center]]

Open and edit a work order.

Click '''Work Order''' in the ''Main Menu''. If you have a work order barcode, go ahead and scan that now to open the work order. If not, click '''Menu''' >> '''Manual input: Work Order'''.

To create a new work order, click '''Menu''' >> '''New Work Order'''.

Click on the link below to watch a VIDEO TUTORIALS for the Work Order screen.

[http://www.youtube.com/watch?v=kCcdj8-yK9k/ Create a New Work Order]

[http://www.youtube.com/watch?v=-XOWb98dFRM/ Find an Existing Work Order]

==Settings==

For the laser to be on at all times (instead of having to click on the button to bring up the laser), on the mobile setting, tap on ITrack LX then turn on the ''Always On''.

[[File:Alwaysonsetting.jpg|center]]

Brother Printer Driver Installation & Configuration

2022-02-03T16:04:41Z

Jwoody: /* Configure the Printer */

=Download the Brother Driver=

The driver can be downloaded from the Brother Support page:
https://support.brother.com/g/b/downloadtop.aspx?c=us&lang=en&prod=lpql820nwbeus&_ga=2.206996560.2128116515.1611171424-1589914554.1584728565

Once the driver is downloaded, run through the installer, following the prompts.

=Adding the Printer=
First, we need to set up the printer so the Presage server can print to it.

# Get the IP of the printer.
# In Control Panel > Devices and Printers, click Add a Printer.
# Click "The Printer I want isn't listed", then click "Add a local or network printer as an administrator" on the following screen.
# Click "The Printer I want isn't listed" again.
# Select "Add a printer using a TCP/IP address or hostname" and click Next.
# In the next screen, enter the IP of the printer in the "Hostname or IP Address" field. The rest of the settings in that window can stay as defaults.
# Give the printer a descriptive name that includes the plant name. Avoid spaces.
# After adding the printer, we need to edit the default paper size. Set it to "Small Address Label".

(Printer can also be installed as USB, but networked is more common.)

=Configure the Printer=
Once the printer is installed on the server, we need to change the default paper size.

# In the Printer control panel, r-click on the printer and select Printing Properties.
# Go to the Advanced tab, and click the Printing Defaults button.
# Set "Paper Size" to:
#*Small Address Label for the standard DK-1209 (orientation: Landscape)
#*Return Address Label (DK-1204) for the tiny (0.66x2.1) labels (orientation: Landscape)
#*2.4" for continuous rolls. Additionally for continuous rolls, set length to 1.10 and orientation to Portrait. Note: Unlike the other sizes, the preview diagram in the corner will look wrong. But it is labeled correctly.
#*2.4" for Work Order ''Testing'' Tags. Additionally for Testing Tags, set length to 0.5 and orientation to Portrait. These have report.type "Work Order Testing Tags" instead of "Work Order Tags" and have a separate field for print quantity override. They are for wrapping around the circumference surface of a petri dish.
# Change the "Quality" to "Give priority to print '''quality''' 300 x 300 dpi" (it starts out as giving priority to print '''speed''')
# New addition as of 3/4/20: in the Advanced tab, select "Halftone: For logo and text Binary" to solve an issue of some barcodes looking okay but not scanning
# Click OK to save changes. (I like to reopen the print preferences window to ensure my changes saved)

The GROUPing pitfall

2020-09-21T15:55:27Z

Jwoody: /* The short version */

The GROUPing pitfall is a very common logical error in SQL queries, especially among people getting familiar with a [http://en.wikipedia.org/wiki/Relational_model relational database].

This is what will happen - you'll be writing a query with a couple JOINs in it. You add a GROUP BY for the correct columns, and everything looks peachy. Then, at some point in the future (could be 5 minutes, could be a month), BOOM! Your query is returning random shit, and you have no clue why.

Considering that we here at [[ISoft]] pay the bills by writing queries that DON'T return random shit, this is something you should probably care about.

== The short version ==
If your query joins on more than one table with a one-to-many relationship with the row you're grouping by, you CANNOT use any [https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html aggregate functions] in your query.

If you do, then any time that more than one of those JOINs matches more than one row, all of your aggregate functions will break.

== The long version ==
You know how JOINs work, right? Let's say you have two tables:

{|- class="wikitable" border=1
|+ Monkey
! id (primary key)
! name
! weight
|- style="color:blue"
| 1
| Bobo
| 220
|- style="color:blue"
| 2
| Bimbo
| 200
|- style="color:blue"
| 3
| The Hooker
| 375
|}

{|- class="wikitable" border=1
|+ Poop
! id (primary key)
! monkeyid
! date
! size
|- style="color:red"
| 1
| 2
| 2010-01-01 13:43:04
| 4.3
|- style="color:red"
| 2
| 3
| 2010-01-02 06:12:44
| 3.8
|- style="color:red"
| 3
| 1
| 2010-01-02 09:14:56
| 4.0
|- style="color:red"
| 4
| 2
| 2010-01-02 15:05:33
| 2.6
|}

As you can infer, there is a one-to-many relationship between "monkey" and "poop". That is to say, one monkey can have any number of poops.

I'm sure you understand what happens when they are joined together:
<source lang="mysql">
SELECT monkey.name AS monkey_name, poop.size AS poop_size
FROM monkey
JOIN poop ON poop.monkeyid = monkey.id
ORDER BY monkey.id
</source>

You get this!

{| border = 1
|+ Query results
! monkey_name
! poop_size
|-
| style="color:blue" | Bobo
| style="color:red" | 4.0
|-
| style="color:blue" | Bimbo
| style="color:red" | 4.3
|-
| style="color:blue" | Bimbo
| style="color:red" | 2.6
|-
| style="color:blue" | The Hooker
| style="color:red" | 3.8
|}

But you already knew that. I mean, that's kid stuff! I didn't even write it out, I actually got one of my younger siblings to type it out because I was so bored by it.

You see that Bimbo shows up twice - the one row in the monkeys table got multiplied by all the matching rows in the poop table that it got joined to. You've seen it before, and if you wanted to use this data in a report that displayed one record per monkey, you would know what to do - use a GROUP BY!

=== A completely valid query ===

<source lang="mysql">
SELECT monkey.name AS monkey_name, AVG(poop.size) AS average_poop_size, SUM(poop.size) AS total_poop,
COUNT(DISTINCT poop.id) AS number_of_poops
FROM monkey
JOIN poop ON poop.monkeyid = monkey.id
GROUP BY monkey.id
</source>

{| border = 1
|+ Query results
! monkey_name
! average_poop_size
! total_poop
! number_of_poops
|-
| style="color:blue" | Bobo
| style="color:red" | 4.00
| style="color:red" | 4.0
| style="color:red" | 1
|-
| style="color:blue" | Bimbo
| style="color:red" | 3.45
| style="color:red" | 6.9
| style="color:red" | 2
|-
| style="color:blue" | The Hooker
| style="color:red" | 3.80
| style="color:red" | 3.8
| style="color:red" | 1
|}

Boom, bitches. Fire up [[Crystal Reports|Crystal]] and turn that sucker into a report, amirite?

=== BUT WAIT! ===
Thanks to my narrative-writing powers, you remember another table that contains data relevant to this report!

{| border = 1
|+ Meal
! id
! monkeyid
! description
! weight
! date
|- style="color:green"
| 1
| 2
| A delicious spaghetti dinner with a side of garlic bread.
| 1.5
| 2010-01-01 19:24:00
|- style="color:green"
| 2
| 3
| Shoots
| 4.7
| 2010-01-01 08:45:55
|- style="color:green"
| 3
| 1
| Leaves
| 3.4
| 2010-01-01 13:17:00
|- style="color:green"
| 4
| 3
| Monkey food
| 5.1
| 2010-01-01 17:43:19
|}

Dude, the eating habits of these monkeys are totally the sort of thing you would want to use in your report on pooping frequency. Let's join that shit into your query!

<source lang="mysql">
SELECT monkey.name AS monkey_name, AVG(poop.size) AS average_poop_size, SUM(poop.size) total_poop,
COUNT(DISTINCT poop.id) AS number_of_poops, COUNT(DISTINCT meal.id) AS number_of_meals
FROM monkey
JOIN poop ON poop.monkeyid = monkey.id
JOIN meal ON meal.monkeyid = monkey.id # This other table has useful data, too!
GROUP BY monkey.id
</source>

{| border = 1
|+ Query results
! monkey_name
! average_poop_size
! total_poop
! number_of_poops
! number_of_meals
|-
| style="color:blue" | Bobo
| style="color:red" | 4.00
| style="color:red" | 4.0
| style="color:red" | 1
| style="color:green" | 1
|-
| style="color:blue" | Bimbo
| style="color:red" | 3.45
| style="color:red" | 6.9
| style="color:red" | 2
| style="color:green" | 1
|-
| style="color:blue" | The Hooker
| style="color:red" | 3.80
| style="color:red" | 7.6
| style="color:red" | 1
| style="color:green" | 2
|}

Well aren't you a query-writing beast! Time to put this query into production code, eh?

=== WRONG! ===
If you were observant, you would have noticed something is amiss with those results - the total_poop of The Hooker is different than it was in the last query! What the junk?

Debugging time - let's run the query without GROUP BY (or the aggregate functions) and see what data we're working with:

<source lang="MySQL">
SELECT monkey.name AS monkey_name, poop.size AS poop_size, poop.id AS poop_id, meal.id AS meal_id
FROM monkey
JOIN poop ON poop.monkeyid = monkey.id
JOIN meal ON meal.monkeyid = monkey.id
</source>

{| border=1
|+ Query results
! monkey_name
! monkey_id
! poop_id
! meal_id
|-
| style="color:blue" | Bimbo
| style="color:blue" | 2
| style="color:red" | 1
| style="color:green" | 1
|-
| style="color:blue" | Bimbo
| style="color:blue" | 2
| style="color:red" | 4
| style="color:green" | 1
|-
| style="color:blue" | The Hooker
| style="color:blue" | 3
| style="color:red" | 2
| style="color:green" | 2
|-
| style="color:blue" | The Hooker
| style="color:blue" | 3
| style="color:red" | 2
| style="color:green" | 4
|-
| style="color:blue" | Bobo
| style="color:blue" | 1
| style="color:red" | 3
| style="color:green" | 3
|}

Or, to try to show how the JOINs work a bit more clearly:
{| border=1
|+ Query results
! monkey_name
! monkey_id
! poop_id
! meal_id
|-
| style="color:blue" rowspan="2" | Bimbo
| style="color:blue" rowspan="2" | 2
| style="color:red" | 1
| style="color:green" | 1
|-
| style="color:red" | 4
| style="color:green" | 1
|-
| style="color:blue" rowspan="2" | The Hooker
| style="color:blue" rowspan="2" | 3
| style="color:red" rowspan="2" | 2
| style="color:green" | 2
|-
| style="color:green" | 4
|-
| style="color:blue" | Bobo
| style="color:blue" | 1
| style="color:red" | 3
| style="color:green" | 3
|}

=== Duh! ===
Of course! As you can see by the poop_id column, poop #2 is getting counted twice! And not only that, but meal #1 is also being counted twice - if we had used any aggregate functions on columns from the meal table, they would have returned fux0red data as well!

By drawing on your basic understanding of how JOINs work, you should quickly see what causes this. Every row that is related to a monkey id is going to be multiplied by the number of matching rows from tables are joined to that monkey id.

Of course, all those multiplied rows disappear from sight as soon as you GROUP BY some column - but the first aggregate function you use will expose the dirty truth: you didn't put any thought into how these tables related!

== How to NOT make the mistake ==
For EVERY SINGLE JOIN you make, you must go through this thought process:
# What is this query grouping by - what is the "primary key" of my result set?
# This table/subquery I'm joining on - what is its relationship to that "primary key" (one-to-one, one-to-many)?
# Does my (pre-GROUP) dataset already have a one-to-many relationship with my final result set?

If it does, then stop what you're doing - you're about to fuck up your results, and probably confuse yourself horribly in the process.

As someone who (I'm assuming) is going to be writing a lot of queries in the future (I don't see us moving to using flat-files as a storage engine any time soon), this is something that will need to become automatic for you.

Brain farts happen, but if you put effort into keeping track of which columns are unique on both sides of your joins, you should be able to make sense of what's going on in your results.

== Protip: write that query correctly ==
How do you make sure that your aggregate functions return the data you want? Well, until MySQL starts reading minds (7.0?), you make sure that '''only one of the data sources in your query has a one-to-many relationship with your result.'''

Like most of life's difficulties, this was made easier with the release of MySQL 4.1!

Want to join some phat data into your query, but don't want to risk multiplying your data set by all the matching rows in another table? Join a subquery!

Subqueries can be joined (and even selected from) just like you would a table:

<source lang="MySQL">
SELECT monkey.name AS monkey_name, poop_grouped.average_poop_size, poop_grouped.total_poop, poop_grouped.poops,
meal_grouped.average_meal_weight, meal_grouped.total_meal_weight, meal_grouped.number_of_meals
FROM monkey
JOIN
(
SELECT poop.monkeyid, AVG(poop.size) AS average_poop_size, SUM(poop.size) AS total_poop, COUNT(*) AS poops
FROM poop
GROUP BY poop.monkeyid
) AS poop_grouped ON poop_grouped.monkeyid = monkey.id
JOIN
(
SELECT meal.monkeyid, AVG(meal.weight) AS average_meal_weight, SUM(meal.weight) AS total_meal_weight,
COUNT(*) AS number_of_meals
FROM meal
GROUP BY meal.monkeyid
) AS meal_grouped ON meal_grouped.monkeyid = monkey.id
</source>

{| border=1
|+ Query results
! monkey_name
! average_poop_size
! total_poop
! poops
! average_meal_weight
! total_meal_weight
! number_of_meals
|-
| style="color:blue" | Bobo
| style="color:red" | 4.00000
| style="color:red" | 4.0
| style="color:red" | 1
| style="color:green" | 3.40000
| style="color:green" | 3.4
| style="color:green" | 1
|-
| style="color:blue" | Bimbo
| style="color:red" | 3.45000
| style="color:red" | 6.9
| style="color:red" | 2
| style="color:green" | 1.50000
| style="color:green" | 1.5
| style="color:green" | 1
|-
| style="color:blue" | The Hooker
| style="color:red" | 3.80000
| style="color:red" | 3.8
| style="color:red" | 1
| style="color:green" | 4.90000
| style="color:green" | 9.8
| style="color:green" | 2
|}

That's right - every data set (the original table, as well as those subqueries) has a 1-to-1 relationship with our final result set. Because of this, we don't even need a GROUP BY in the main query!

This query is also valid:

<source lang="MySQL">
SELECT monkey.name AS monkey_name, AVG(poop.size) AS average_poop_size, SUM(poop.size) AS total_poop,
COUNT(poop.id) AS poops, meal_grouped.average_meal_weight, meal_grouped.total_meal_weight,
meal_grouped.number_of_meals
FROM monkey
JOIN poop ON poop.monkeyid = monkey.id
JOIN
(
SELECT meal.monkeyid, AVG(meal.weight) AS average_meal_weight, SUM(meal.weight) AS total_meal_weight,
COUNT(*) AS number_of_meals
FROM meal
GROUP BY meal.monkeyid
) AS meal_grouped ON meal_grouped.monkeyid = monkey.id
GROUP BY monkey.id
</source>

You just have to bear in mind that in your main query, the rows in `monkey` are being multiplied by the number of matches in `poop`, and the GROUP BY combines them safely.

Adding another JOIN with a one-to-many relationship to any of the data already in the query would result in a massive fail.

== To summarize ==
Logical errors in your queries are easy to miss. If something looks wrong in your results, check all the places that data is coming from - look at the relationships between the data sets that contribute to your final results.

If you don't pay close attention, and don't happen to catch problems during testing, your bad query will sit in production until some customer calls up wondering why some of the numbers on their screen "look weird". And that's just embarrassing.

User:Jwoody

2020-09-14T18:43:17Z

Jwoody: Woody's Employee Page

**placeholder**

I'll come up with something witty after finishing onboarding stuffs.