Utilizing BigQuery's Group By function on multiple columns allows for flexible ordering without impacting the results

I am attempting to group the data in this table so that it will be displayed as shown below.

Original Data

 Student  Fruit 1  Fruit 2
 Tom      Apple    Banana
 Tom      Banana   Apple
 Gary     Apple    Banana

Grouped Data

 Student  Fruit 1  Fruit 2  Count  Repeated Condition 
 Tom      Apple    Banana   2      True
 Gary     Apple    Banana   1      False

Does anyone know how I can achieve this easily using Google BigQuery? The order is not important. I have experimented with String_AGG and Concat functions but the order matters.

Working on developing a function that aims to create the desired grouped table.

javascriptsqlgoogle-apps-scriptgoogle-bigquery

Answer №1

To organize the fruits in two columns alphabetically, utilize LEAST and GREATEST. Then, group them based on these sorted values:

SELECT Student,
       LEAST(Fruit1, Fruit2) AS Fruit1,
       GREATEST(Fruit1, Fruit2) AS Fruit2,
       COUNT(*) AS Count,
       CASE WHEN COUNT(*) > 1 THEN 'True' ELSE 'False' END AS "Repetition Check"
FROM fruits
GROUP BY Student, LEAST(Fruit1, Fruit2), GREATEST(Fruit1, Fruit2)

Result:

student     fruit1  fruit2  count   Repetition Check
Tom         Apple   Banana  2       True
Gary        Apple   Banana  1       False

Answer №2

Here is a technique to achieve the desired reporting outcome using a minimum/maximum approach:

SELECT
    Student,
    LEAST(fruit1, fruit2) AS fruit1,
    GREATEST(fruit1, fruit2) AS fruit2,
    COUNT(*) AS count,
    CASE WHEN LEAST(fruit1) <> GREATEST(fruit1)
         THEN 'True' ELSE 'False' END AS "Repeated Condition"
FROM yourTable
GROUP BY
    Student,
    LEAST(fruit1, fruit2),
    GREATEST(fruit1, fruit2)

The concept involves grouping by student and finding the smaller/larger values within pairs of fruits. This accounts for scenarios like (Apple, Banana) being considered equivalent to (Banana, Apple). The "Repeated Condition" is set to True if there was ever a discrepancy in the order of fruits within a group of students and fruits; otherwise, it is set to False.

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

AngularJS handles intricate JSON responses effortlessly

I have been learning angularjs on my own, mostly from w3schools and other websites. I have been trying to download and parse some JSON data. I was successful with a simple JSON url (http://ip.jsontest.com), but I am struggling with a more complex response. ...

javascriptjsonangularjshtmlweb-services

Searching for a streamlined approach to sending out numerous HTTP requests in a node.js environment

I'm new to the world of JS/node.js after working with .Net. I have an existing Web API host that I want to stress test with different payloads. I am aware of load testing tools available for this purpose, but my focus right now is on finding an effic ...

javascriptnode.jshttppost

Arranging results in a selection query. PostgreSQL

Is the order of records in a SELECT QUERY always consistent in the results? For example, if the first query returns the sequence: first record second record third record Will all subsequent 'select * from t' queries always return records in the ...

sqlpostgresql

Sending a Django form using AJAX and jQuery: A step-by-step guide

After researching various tutorials on django AJAX forms, I've found that each one offers a different method, but none of them seem simple. Being new to AJAX, I'm feeling a bit confused by the complexity involved. In my specific case, I have a m ...

javascriptjqueryajaxdjangodjango-templates

Insert data into Postgres table, utilizing on-conflict resolution and foreign key validation

Question: ADD TO CART products (name, price, quantity) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price, quantity = EXCLUDED.quantity WHERE EXISTS (select id from product where id = ?) Please note, Th ...

sqlpostgresql

Discovering the form through a click event using jQuery

$(function() { $('.offer-wrapper').click(function (e) { if ($(e.target).hasClass('form')) { return // perform an action if a form is present } }); }); <script src="https://cdnjs.cloudflare.com/ajax/lib ...

javascriptjqueryhtml

Get JavaScript to output Vue data variables

Currently, I am exploring VUE and creating dynamic elements based on data using v-model inputs. Below is an example code snippet where I am attempting to integrate jQuery/JS functionality to enable users to adjust font sizes within each created element usi ...

javascriptjqueryvue.js

Utilizing jQuery to Maintain State Across Page Refreshes with Cookies

Currently, I am attempting to create a script that can retain its state even after the page is refreshed. Here is my progress so far: Utilizing jQuery: $(window).ready(function() { var voteId = $('.gallery-item a'); $(voteId).click(function() ...

javascriptjqueryhtmlperformancecookies

Prevent the page from refreshing when a value is entered

I currently have a table embedded within an HTML form that serves multiple purposes. The first column in the table displays data retrieved from a web server, while the second column allows for modifying the values before submitting them back to the server. ...

javascripthtmlforms

Using VUE.JS to trigger a function that applies discounts prior to form submission

Before submitting my form with Axios, I am in need of applying specific discounts to each item within campaign.items. To achieve this, a functional method has been created: applyDiscount(price) { return price - (this.totalDiscount * price) }, Pri ...

javascriptvue.js

Scroll Function Activates Repeatedly Instead of Triggering Just Once

I'm working on a website that smoothly scrolls to each section with a single scroll action. This involves detecting whether the page is being scrolled up or down. The code I've written seems to address this issue, but it triggers the scroll actio ...

javascriptjqueryarraysscrollinfinite-scroll

I'm curious if it's possible to create a scrolling marquee on an SVG path with the use of HTML and CSS?

I am interested in creating a unique effect similar to what is showcased on this website or in the main menu of Colin McRae Rally 2.0. My goal is to generate a path and animate text along it, with the letters wrapping around to the start as they reach the ...

javascripthtmlcsssvg

Position a BoxGeometry in a straight line between two points in 3D space

In my game, I have the ability for players to add objects to a world without being restricted to a grid. Now, I am working on adding footpaths to the game. When the player clicks on "Create footpath", they can add a point in the world at the location of th ...

javascriptthree.js

PHP object representing a datetime in JSON format

How can I convert a JSON datetime object sent to JavaScript into a JavaScript datetime object? The PHP return is: "date": { "lastErrors": { "warning_count":0, "warnings":[], "error_count":0, "errors":[] }, "timezone": { "nam ...

javascriptphpjson

incorrect execution of SQL query

Ever since I switched my operating system from Windows 7 to Linux Mint, my PHP/SQL code seems to be malfunctioning. $AddUserToDatabase = $DatabaseConnection->prepare( 'INSERT INTO gebruikers (' . ' gebruiker_naam, profiel_foto ...

phpmysqlsqlpdophpmyadmin

Restart the calling process using NodeJS command

Is there a way to automatically restart the calling process in case certain events occur while querying a database? I want the process to start over if specific conditions are met. ...

javascriptnode.js

Transferring information to PHP script using only JavaScript (Ajax)

I have a good understanding of jQuery: $.ajax({ method: 'POST', url: 'send-data-to.php', data: {data:"data"} }) However, I am unsure of how to send data that is accessible via $_POST using pure JavaScript. The reason for my inqu ...

javascriptphpajax

Express server failing to serve js and css files

Currently, I'm working on a ReactJS project with React Router implemented. When attempting to access /dashboard/stream, the server is returning the index.html file successfully. However, there are 301 errors for the CSS and JS files, resulting in noth ...

javascriptreactjsexpress

React - Implementing pre-fetching of data prior to rendering

As a beginner in react, I have encountered a best practice dilemma that is causing me to make mistakes. My issue arises when I call an API to get information and then update an array in the state once the response is received. However, in the "render" func ...

javascriptreactjsreduxreact-redux

Utilizing Ajax in PHP to Upload Files

I'm encountering an issue while attempting to upload a file and send it via AJAX. The error message I am receiving is as follows: Notice: Undefined index: xlsFile in Here is the code snippet: HTML FORM : (this form is within a Modal Popup) <f ...

javascriptphpjqueryajax