GROUP BY in SQLite

GROUP BY Example in SQLite using count,sum,average,HAVING 

• First create table.
• CREATE TABLE  x ( a, b );
  INSERT INTO x VALUES ( 1, ‘Alice’ );
  INSERT INTO x VALUES ( 2, ‘Bob’ );
  INSERT INTO x VALUES ( 3, ‘Charlie’ );
• CREATE TABLE y ( c, d );
  INSERT INTO y VALUES ( 1, 3.14159 );
  INSERT INTO y VALUES ( 1, 2.71828 );
  INSERT INTO y VALUES ( 2, 1.61803 );
• CREATE TABLE z ( a, e );
  INSERT INTO z VALUES ( 1, 100 );
  INSERT INTO z VALUES ( 1, 150 );
  INSERT INTO z VALUES ( 3, 300 );
  INSERT INTO z VALUES ( 9, 900 );
• select a, count(a) as count from z group by a;
  |a|count
  |1|2
  |3|1
  |9|1
• select a, sum(e) AS total from z group by a;
  |a|total
  |1|250
  |3|300
  |9|900
• sqlite> select a, sum(e), count(e),
  …> sum(e)/count(e) as expr, avg(e) as agg
  …> from z group by a;
  |a| sum(e) |count(e)    |expr| agg
  |1| 250     |2     |125| 125.0
  |3| 300     |1     |300| 300.0
  |9| 900     |1     |900| 900.0
• select a, sum(e) as

For More Information Please Visit

swamios