IOE 373 Lecture 13: IOE373_Lab9
Document Summary
Querying in sql to create tables for analysis. Iif ( boolean_expression, true_value, false_value : using inner join and and together, group by with aggregates. Using the access file puchasesdatabase_lab9 in ctools, complete the following queries. Then, submit each of the queries to ctools in a word document: you want to analyze spending by payment type. In order to do this, create a sql query which gives you the count of transactions between 0 and 10, the count of transactions between 10 and. 100, the count of transactions between 100 and 1000, the count of transactions over 1000, the number of purchases, and the total revenue for each payment type in ascending order of payment type. (numtransactionbyamountrange) Select paymenttype, sum(iif(0 <= totalprice and totalprice < 10, 1, 0)) as cnt_0_10_usd, Sum(iif(10 <= totalprice and totalprice < 100,1,0)) as cnt_10_100usd, sum(iif(100 <= totalprice and totalprice < 1000,1,0)) as cnt_100_1000usd, sum(iif(totalprice >= 1000,1,0)) as cnt_1000usd, count(*) as cnt, format(sum(totalprice),currency) as revenue.