CODEWITHSUNDEEP
sqldategroup-bycountgoogle-bigquery
Zayn_SE
Zayn_SE

Reputation: 173

Get count of records per day for all records in SQL Bigquery, subquery in select statement

I'm trying to write a query that uses a subquery to generate a column with the count of records. I've written the code below and in the table after the code, when I remove the subquery I do generate columns messages and count_date but am not able to generate columns terr and sat (these columns would be within the subquery). I've been able to do it for one record of history but not when I use an interval more than 1.

what do I need to change in my subquery to get sat and terr 

SELECT count(*) as TotalMessages, Date(ingestion_time) as Date_ingestion, 
  (SELECT count(*) 
    FROM myTable
    WHERE collection_type = '1' AND date(ingestion_time) >=                       
 (DATE_SUB(CURRENT_DATE(), INTERVAL 350 DAY)) AND
      date(ingestion_time) < (CURRENT_DATE())
      GROUP BY date(ingestion_time)
      ) AS terr                      
FROM 
  myTable
WHERE 
  date(ingestion_time) >= (DATE_SUB(CURRENT_DATE(), INTERVAL 350 DAY)) AND
      date(ingestion_time) < (CURRENT_DATE())
GROUP BY 2
ORDER BY 2;

My idea is below, for me I'll be going back over 3 months. 

Messages      | count_date | terr | sat 
500           | 2019-11-13 | 486  | 14
710           | 2009-11-12 | 700  | 10
450           | 2009-11-11 | 440  | 10
767           | 2009-11-10 | 760  | 7

and so forth until the last possible count from a day.

Upvotes: 0

Views: 610

Answers (1)

GMB
GMB

Reputation: 222662

By looking at your query and expected results, I suspect that you are actually looking for conditional aggregation. If so, there is no need for a subquery, you can simply do conditional sums, as follows:

SELECT 
    count(*) as TotalMessages, 
    Date(ingestion_time) as Date_ingestion,
    sum(case when collection_type = '1' then 1 end) as terr,
    sum(case when not collection_type = '1' then 1 end) as sat
FROM 
  myTable
WHERE 
  date(ingestion_time) >= (DATE_SUB(CURRENT_DATE(), INTERVAL 350 DAY)) AND
      date(ingestion_time) < (CURRENT_DATE())
GROUP BY 2
ORDER BY 2;

Upvotes: 3

Related Questions