Page 86 - SQL
P. 86
Customer Credit Debit
Peter 400 0
John 1000 500
select customer,
sum(case when payment_type = 'credit' then 1 else 0 end) as credit_transaction_count,
sum(case when payment_type = 'debit' then 1 else 0 end) as debit_transaction_count
from payments
group by customer
Result:
Customer credit_transaction_count debit_transaction_count
Peter 2 0
John 1 1
AVG()
The aggregate function AVG() returns the average of a given expression, usually numeric values
in a column. Assume we have a table containing the yearly calculation of population in cities
across the world. The records for New York City look similar to the ones below:
EXAMPLE TABLE
city_name population year
New York City 8,550,405 2015
New York City ... ...
New York City 8,000,906 2005
To select the average population of the New York City, USA from a table containing city names,
population measurements, and measurement years for last ten years:
QUERY
select city_name, AVG(population) avg_population
from city_population
where city_name = 'NEW YORK CITY';
Notice how measurement year is absent from the query since population is being averaged over
time.
https://riptutorial.com/ 68
