CODEWITHSUNDEEP
google-bigquery
user20184534
user20184534

Reputation: 41

How to find the highest sales in each year in BigQuery?

The following table contains phone name,number of items sold,month and year.

with table1 as(
select "iphone" as phone,3 as sold_out,"Jan" as month,2015 as year union all
select "iphone",10,"Feb",2015 union all
select "samsung",4,"March",2015 union all
select "Lava",14,"June",2016 union all
select "Lenova",8,"July",2016 union all
select "Lenova",10,"Sep",2016  union all
select "Motorola",8,"Jan",2017 union all
select "Nokia",7,"Jan",2017 union all
select "Nokia",3,"Feb",2017
)

and I would to get the answer like this

-----------------------------
year      Phone      sales
-----------------------------
2015      iphone     13
2016      lenova     18
2017      Nokia      10
-----------------------------

I haven't tried because honestly I don't know

Upvotes: 0

Views: 426

Answers (2)

Mangu
Mangu

Reputation: 3325

SELECT year AS year, phone AS Phone, sum(sold_out) AS sales
FROM table1
GROUP BY year, Phone
HAVING COUNT(Phone)=2
ORDER BY year ASC
;

This will give you the output that you desire, in Standard SQL.

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Below is for BigQuery Standrad SQL 

#standardSQL
SELECT 
  year, 
  ARRAY_AGG(STRUCT(phone, sales) ORDER BY sales DESC LIMIT 1)[OFFSET(0)].*
FROM (
  SELECT year, phone, SUM(sold_out) sales
  FROM `project.dataset.table1`
  GROUP BY year, phone
)
GROUP BY year

You can test / play above with dummy data from your question as below 

#standardSQL
WITH `project.dataset.table1` AS(
  SELECT "iphone" AS phone,3 AS sold_out,"Jan" AS month,2015 AS year UNION ALL
  SELECT "iphone",10,"Feb",2015 UNION ALL
  SELECT "samsung",4,"March",2015 UNION ALL
  SELECT "Lava",14,"June",2016 UNION ALL
  SELECT "Lenova",8,"July",2016 UNION ALL
  SELECT "Lenova",10,"Sep",2016  UNION ALL
  SELECT "Motorola",8,"Jan",2017 UNION ALL
  SELECT "Nokia",7,"Jan",2017 UNION ALL
  SELECT "Nokia",3,"Feb",2017
)
SELECT 
  year, 
  ARRAY_AGG(STRUCT(phone, sales) ORDER BY sales DESC LIMIT 1)[OFFSET(0)].*
FROM (
  SELECT year, phone, SUM(sold_out) sales
  FROM `project.dataset.table1`
  GROUP BY year, phone
)
GROUP BY year
ORDER BY year

with result 

Row year    phone   sales    
1   2015    iphone  13   
2   2016    Lenova  18   
3   2017    Nokia   10

Upvotes: 3

Related Questions