Search code examples
google-visualizationgoogle-query-language

Invalid query: SELECT_WITH_AND_WITHOUT_AGG in Google Query Language

I'm trying to create a piechart from a single column of data with the google visualization API by counting each value, but I keep getting the following error message:

Invalid query: SELECT_WITH_AND_WITHOUT_AGG: C

INFORMATION

My spreadsheet can be found here: https://docs.google.com/spreadsheets/d/1lmmpJs2Bz3EfQWExB4KXq_uJWoLlq1PMCahy6w4ipcE/edit#gid=942634171

What I want to do is create a pie chart that counts the unique values in the 'What neighborhood are you from' column and plot their relative proportions.

The column itself can be seen here:

Picture of spreadsheet column

So I'd like to take this one column and create a table from it that reads like this:

EXAMPLE TABLE

Corona                     2
Sunnyside                  3
Elmhurst                   4

And so on. And then use the string/number combination to populate a pie chart.

With regular SQL you could do SELECT Column, COUNT(Column) GROUP BY Column, so I'm assuming something similar would work for GQL.

So far these are some of the queries I've tried:

QUERIES:

'SELECT COUNT(C) GROUP BY C'
'SELECT C, COUNT(C) GROUP BY C'
'SELECT C, COUNT(C) PIVOT C

But none of these have worked.

I also get this error message in my console:

ERROR MESSAGE:

Console message

Here's the script I'm using that pertains to my problem:

SCRIPT

// Load the Visualization API and the controls package.
  google.charts.load('current', {'packages':['corechart', 'controls']});

  // Set a callback to run when the Google Visualization API is loaded.
  google.charts.setOnLoadCallback(drawChart);
    function drawChart() {
        var neighborhoodChart = new google.visualization.ChartWrapper({
        'chartType'     : 'PieChart',
        'containerId'   : 'chart_div2',
        'dataSourceUrl' : 'https://docs.google.com/spreadsheets/d/1lmmpJs2Bz3EfQWExB4KXq_uJWoLlq1PMCahy6w4ipcE/gviz/tq?gid=942634171',
        'query'         : 'SELECT C, COUNT(C) GROUP BY C',
        'options'       : {
            'title'     : 'Neighborhood of Residence'
        }
    });

  neighborhoodChart.draw();
  }

Here's the script for the entire page that it's being used in:

ENTIRE SCRIPT

<!DOCTYPE html>
<html lang="en">

<head>
<title>CDC Dashboard</title>

<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">

<!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">

<!-- Bootswatch Theme -->
<link rel="stylesheet" type="text/css" href="https://maxcdn.bootstrapcdn.com/bootswatch/3.3.7/flatly/bootstrap.min.css" >

<!-- External style sheet -->
<link rel="stylesheet" type="text/css" href="styles.css">

<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>

<!-- Latest compiled and minified JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>

<!--Load the AJAX API for Google Charts-->
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>

<script type="text/javascript"
  src='https://www.google.com/jsapi?autoload={
    "modules":[{
    "name":"visualization",
    "version":"1"
    }]
  }'></script>

<script type="text/javascript">

  // Load the Visualization API and the controls package.
  google.charts.load('current', {'packages':['corechart', 'controls']});

  // Set a callback to run when the Google Visualization API is loaded.
  google.charts.setOnLoadCallback(drawChart);
    function drawChart() {
        var neighborhoodChart = new google.visualization.ChartWrapper({
        'chartType'     : 'PieChart',
        'containerId'   : 'chart_div2',
        'dataSourceUrl' : 'https://docs.google.com/spreadsheets/d/1lmmpJs2Bz3EfQWExB4KXq_uJWoLlq1PMCahy6w4ipcE/gviz/tq?gid=942634171',
        'query'         : 'SELECT C, COUNT(C) GROUP BY C',
        'options'       : {
            'title'     : 'Neighborhood of Residence'
        }
    });

  neighborhoodChart.draw();
  }

</script>
</head>

<body>

<!-- Navbar to be affixed at the top -->
<nav class="navbar navbar-inverse">
    <div class="container-fluid">
        <div class="navbar-header">
            <button type="button" class="navbar-toggle" data-toggle="collapse" data-target="#myNavbar">
              <span class="icon-bar"></span>
              <span class="icon-bar"></span>
              <span class="icon-bar"></span>
            </button>
        </div>
        <div>
          <div class="collapse navbar-collapse" id="myNavbar">
            <ul class="nav navbar-nav">
              <li><a href="index.html">What We Do</a></li>
              <li><a href="serve.html">Who We Serve</a></li>
            </ul>
          </div>
        </div>
    </div>
</nav>


<div class="container-fluid">
    <div class="col-md-10 col-md-offset-1">
        <h1 class="text-center">Who We Serve</h1>

        <div class="row">
            <div class="col-md-8 col-md-offset-2">
                <hr>
            </div>
        </div>

          <h2 class="text-center">Understanding Our Clients</h2>

          <div id="dashboard_div">
              <!--Divs that will hold each control and chart-->
              <div class="row">
                <div class="col-md-6">
                    <div id="filter_div"></div>
                </div>
                <div class="col-md-6">
                    <div id="search_div"></div>
                </div>
              </div>

              <div class="row">
                <div class="col-md-6">
                    <div id="attendance_div"></div>
                </div>
                <div class="col-md-6">
                    <div id="transport_div"></div>
                </div>
              </div>

              <div id="chart_div">
              </div>

              <div id="chart_div2">
              </div>
          </div>

          <div class="row">
            <div class="col-md-8 col-md-offset-2">
                <hr>
            </div>
          </div>

        <div id="storytime">
            <h2 class="text-center">Storytime</h2>
        </div>
    </div>
</div>
</body>

</html>

The working file for it can be seen here: https://s3-us-west-2.amazonaws.com/example-server/serve.html

UPDATE

Ideally I'd prefer a solution that only relies on changing the query itself, as this is syntactically cleaner. I'll be doing this several times throughout the project and would prefer to avoid workarounds that involve extra lines of code.

Any help would be greatly appreciated.

Solution

  • this should work, must be a bug or something...

    'SELECT C, COUNT(C) GROUP BY C'

    regardless, you can use data.visualization.data.group to manually aggregate the column

    see following working snippet... 

    google.charts.load('current', {
  callback: drawChart,
  packages: ['controls', 'corechart', 'table']
});

function drawChart() {
  var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1lmmpJs2Bz3EfQWExB4KXq_uJWoLlq1PMCahy6w4ipcE/gviz/tq?gid=942634171');
  query.setQuery('SELECT C');
  query.send(function (response) {
    if (response.isError()) {
      alert('Error: ' + response.getMessage() + ' - ' + response.getDetailedMessage());
      return;
    }

    var dataGroup = google.visualization.data.group(
      response.getDataTable(),
      [0],
      [{
        aggregation: google.visualization.data.count,
        column: 0,
        label: response.getDataTable().getColumnLabel(0),
        type: 'number'
      }]
    );

    var neighborhoodChart = new google.visualization.ChartWrapper({
      chartType: 'PieChart',
      containerId: 'chart_div',
      dataTable: dataGroup,
      options: {
        height: 240,
        title: 'Neighborhood of Residence'
      }
    });
    neighborhoodChart.draw();
  });
}
    <script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>