Sunday, April 5, 2015

yogi_Pull From Sheet Named 'Raw' First_Name And Last_Name Of Those People Who Have Run_Miles Done Push-Ups And Done Sit-Ups As Specified In Column C


                                           Google Spreadsheet   Post  #1938
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-05, 2015
question by Gerry Doyon:
https://productforums.google.com/forum/#!mydiscussions/docs/C5iTcddrrKk
Google Sheets: Need help with a SELECT statement
I have a Google Sheet that logs exercise data for people. Here are the columns:

Row B = First name
Row C = Last name
Row D = Miles run
Row F = Push ups
Row G = Sit ups

Each day, or multiple times during the day, people enter their data in the associated form. I created an spreadsheet that runs a number of different queries.

What I want to do now is display a list of all the people who have achieved a specific goal of:

Total miles run >= 100
Total push ups >= 1,000
Total sit ups >= 1,000

The following query showing their totals works:

Select B, C, Sum(D), Sum(E), Sum(F) GROUP by C,B LABEL B 'First Name', C      'Last Name', SUM(D) 'Total Miles', SUM(E) 'Total Push Ups', SUM(F) 'Total Sit Ups'`

Now I want to ONLY show people who have met or exceeded the goals listed above. I tried the following query, which doesn't work:

`Select B, C, Sum(D), Sum(E), Sum(F) WHERE Sum(D) >= 100 AND Sum(e) >=1000 AND Sum(F) >= 1000 GROUP by C,B LABEL B 'First Name', C 'Last Name', SUM(D) 'Total Miles', SUM(E) 'Total Push Ups', SUM(F) 'Total Sit Ups'`

Can anyone offer a suggestion that would make this work?
---
Hi all,

Yes, there are a few label types. Here is a duplicate copy of the spreadsheet:


All of the data I sift through is in the "Raw Data" tab. All of my queries are in the "Queries" tab. 

If I just use a select statement that has something like "Where D>=100 OR E>=1000 OR F>=1000 " it will ONLY bring back the data entries that have met or exceeded this criteria. The problem is, there are only one or two people that recorded 100 miles, 1000 pushups and 1000 situps in ONE entry. there are probably ten people who, if you SUM all of their entries, will meet the criteria.

The problem is that Google doesn't seem to allow "SUM(C) AND SUM(D)" in the SELECT statement.

Row 44 of the Queries tab is where I am trying to get the SELECT statement correct. A copy lines down in the same tab, B46, is where I am testing the query. As you can see, only two entries are returned in the result, which is inaccurate.

Thanks!
--------------------------------------------------------------------------------------------------------------------------