Is there a way to output total rows of queries as new columns in SQL?

Corey N 0
Viewed 59

I'm trying to find a way to take my parts of code, and run them all at once to create new columns with the amount of rows (my equals values I've recorded) as new columns, so I can then use them to graph my data.

I am using BigQuery sandbox to try and learn how to do all this without using spreadsheet formulas. Any help is appreciated.

SELECT  member_casual
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE member_casual = 'member'

-- Member Riders = 393357

SELECT  member_casual
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE member_casual = 'casual'

-- Casual Riders = 363890



-- * SUNDAY *

SELECT day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 1 

-- Sunday Riders Total = 115302

SELECT member_casual,day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 1 AND member_casual = 'member'

-- Sunday Member Riders = 46641

SELECT member_casual,day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 1 AND member_casual = 'casual'

-- Sunday Casual Riders = 68661



-- * MONDAY *

SELECT day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 2 

-- Monday Riders Total = 92627

SELECT member_casual,day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 2 AND member_casual = 'member'

-- Monday Member Riders = 49180

SELECT member_casual,day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 2 AND member_casual = 'casual'

-- Monday Casual Riders = 43447


-- * TUESDAY *

SELECT day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 3

-- Tuesday Riders Total = 83822

SELECT member_casual,day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 3 AND member_casual = 'member'

-- Tuesday Member Riders = 52000

SELECT member_casual,day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 3 AND member_casual = 'casual'

-- Tuesday Casual Riders = 31822



-- * WEDNESDAY *

SELECT day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 4

-- Wednesday Riders Total = 115144

SELECT member_casual,day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 4 AND member_casual = 'member'

-- Wednesday Member Riders = 70055

SELECT member_casual,day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 4 AND member_casual = 'casual'

-- Wednesday Casual Riders = 45089



-- * THURSDAY *

SELECT day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 5

-- Thursday Riders Total = 122160

SELECT member_casual,day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 5 AND member_casual = 'member'

-- Thursday Member Riders = 71526

SELECT member_casual,day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 5 AND member_casual = 'casual'

-- Thursday Casual Riders = 50634



-- * FRIDAY *

SELECT day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 6

-- Friday Riders Total = 102945

SELECT member_casual,day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 6 AND member_casual = 'member'

-- Friday Member Riders = 53043

SELECT member_casual,day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 7 AND member_casual = 'casual'

-- Friday Casual Riders = 49902



-- * SATURDAY *

SELECT day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 7

-- Saturday Riders Total = 124147

SELECT member_casual,day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 7 AND member_casual = 'member'

-- Saturday Member Riders = 49812

SELECT member_casual,day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 7 AND member_casual = 'casual'

-- Saturday Casual Riders = 74335

Ideally I'd love something like:

SELECT member_casual,day_of_week
FROM `coreyneergaard-capstone.analyze.2021-09-times` 
WHERE day_of_week = 1 AS total_sunday_riders
WHERE day_of_week = 1 AND member_casual = 'member' AS sunday_member_riders
WHERE day_of_week = 1 AND member_casual = 'casual' AS sunday_casual_riders
WHERE day_of_week = 2 AS total_monday_riders
WHERE day_of_week = 2 AND member_casual = 'member' AS monday_member_riders
WHERE day_of_week = 2 AND member_casual = 'casual' AS monday_casual_riders
...
...
...

UPDATE - I found out how to make it happy and have ended up with this, which should theoretically work once fed all calculations. I wish it only projected to the first row, but I can work with it.

SELECT ride_length, member_casual, day_of_week, (
  SELECT COUNT(*)
  FROM `coreyneergaard-capstone.analyze.2021-09-times` 
  WHERE member_casual = 'casual'
  ) AS casual_riders,
  (SELECT COUNT(*)
  FROM `coreyneergaard-capstone.analyze.2021-09-times` 
  WHERE member_casual = 'member'
  ) AS member_riders 
  FROM `coreyneergaard-capstone.analyze.2021-09-times` 
  GROUP BY ride_length, member_casual, day_of_week
pivot google-bigquery sql
1 Answers

I think you're looking for pivot, but it's a little complicated because you've got two dimensions. Below is a solution where you combine them into a single column by concatenating them with an underscore character:

SELECT
    [0_casual] AS [sunday_casual_riders],
    [0_member] AS [sunday_member_riders],
    [1_casual] AS [monday_casual_riders],
    [1_member] AS [monday_member_riders],
    [2_casual] AS [tuesday_casual_riders],
    [2_member] AS [tuesday_member_riders],
    [3_casual] AS [wednesday_casual_riders],
    [3_member] AS [wednesday_member_riders],
    [4_casual] AS [thursday_casual_riders],
    [4_member] AS [thursday_member_riders],
    [5_casual] AS [friday_casual_riders],
    [5_member] AS [friday_member_riders],
    [6_casual] AS [saturday_casual_riders],
    [6_member] AS [saturday_member_riders]
FROM    (   SELECT  (       CAST(day_of_week AS NVARCHAR(MAX)) 
                        +   '_' 
                        +   member_casual 
                    ) [day_and_type]
            FROM    `coreyneergaard-capstone.analyze.2021-09-times`
        ) PivotableData
PIVOT   (   COUNT(1)
            FOR [day_and_type] 
            IN  (   [0_casual], [0_member],
                    [1_casual], [1_member],
                    [2_casual], [2_member],
                    [3_casual], [3_member],
                    [4_casual], [4_member],
                    [5_casual], [5_member],
                    [6_casual], [6_member]
                )
        ) PivotedData

I think you can actually do this cleaner (and possibly faster) with multiple pivot statements, but I don't trust myself to get the syntax correct without being able to set up sample data and try it out.

Luke Kubat0
Related