CODEWITHSUNDEEP
sqlgoogle-bigquery
noobsmcgoobs
noobsmcgoobs

Reputation: 2746

SQL store results table with month name

I have several CSV's stored to query against. Each CSV represents a month of data. I would like to count all the records in each CSV and save that data to a table as a row in the table. For instance, the table that represents May should return something that looks like this with June following. The data starts in Feb 2018 and continues to Feb 2019 so year value would be needed as well. 

Month    Results
----------------
May 18     1170
June 18    1167

I want to run the same query against all the tables for purposes of efficiency. I also want the query to work with all future updates eg. a March 19 table gets added, and the query will still work.

So far, I have this query.

SELECT COUNT(*)
FROM `months_data.*`

I am querying in Google Big Query using Standard SQL.

Upvotes: 0

Views: 99

Answers (2)

Elliott Brossard
Elliott Brossard

Reputation: 33705

It sounds like you just want an aggregation that counts rows for each month:

SELECT
  DATE_TRUNC(DATE(timestamp), MONTH) AS Month,
  COUNT(*) AS Results
FROM `dataset.*`
GROUP BY month
ORDER BY month

You can use the DATE_FORMAT function if you want to control the formatting.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You seem to need union all:

select 2018 as yyyy, 2 as mm, count(*) as num
from feb2018
union all
select 2018 as yyyy, 3 as mm, count(*)
from mar2018
union all
. . .

Note that you have a poor data model. You should be storing all the data in a single table with a date column.

Upvotes: 0

Related Questions