--------------------------------------------------------------------------------
-- CSE 414 -- Lectures 4
-- AGGREGATES IN SQL
-- Readings: 6.3, 6.4
--------------------------------------------------------------------------------

-- In this lecture we will use the following schema:

create table Purchase(
   pid int primary key,
   product text,
   price float,
   quantity int,
   month varchar(15));

-- download the file lec04-data.txt in the current directory
-- use .import to import the data; see .help
-- note that other database systems have different ways to import data

.import lec04-data.txt Purchase

update purchase set price = null where price = 'null';

-- the five basic aggregate operations

select count(*) from purchase;

select count(quantity) from purchase;

select sum(quantity) from purchase;

select avg(price) from purchase;

select max(quantity) from purchase;

select min(quantity) from purchase;

-- Null values are not used in the aggregate

insert into Purchase values(12, 'gadget', NULL, NULL, 'april');

select count(*) from purchase;
select count(quantity) from purchase;
select sum(quantity) from purchase;
select sum(quantity) from purchase where quantity is not null;  -- same thing

-- Counting the number of distinct values

select count(product) from purchase;
select count(distinct product) from purchase;
