Question

I need to make three queries on the table that I have created. The schema for the table is below.

CREATE TABLE IF NOT EXISTS SalesJan2009
   (transaction_date String, product String, price Float, payment_type String,
   name String, city String, state String, country String, account_created String,
   last_login String, latitude Float, longitude Float)
   ROW FORMAT DELIMITED
   FIELDS TERMINATED BY ','
   LINES TERMINATED BY '\n'
   STORED AS TEXTFILE
   tblproperties("skip.header.line.count"="1");

Queries begin here:

Write a query that counts the number of records for each country.

Write a query that counts the number of orders for each product for each country.

Write a query that calculates the total sales for each product for each country (i.e., sum of price).

Answer 1

If you have any doubts, please give me comment...

-- Write a query that counts the number of records for each country.

SELECT country, COUNT(*) AS noOfRecords

FROM SalesJan2009

GROUP BY country;

-- Write a query that counts the number of orders for each product for each country.

SELECT country, product, COUNT(*) AS noOfOrders

FROM SalesJan2009

GROUP BY country, product;

-- Write a query that calculates the total sales for each product for each country (i.e., sum of price).

SELECT country, product, SUM(price) AS totalSales

FROM SalesJan2009

GROUP BY country, product;