# Group and Join Operations

This chapter lists code examples about group operation and joins, including GROUP BY, HAVING, Perform equi-join on same-level tables, Perform equi-join on main table and subtable, Non-equi-joins, and LEFT JOIN/FULL JOIN.

## GROUP BY

 A 1 =demo.query("select * from EMPLOYEE") 2 =A1.groups(DEPT; sum(SALARY): SalarySum) Group and aggregate together 3 =A1.group(DEPT) First group 4 =A3.new(DEPT,~.count():EmployeeNumber) Then aggregate

## HAVING

 A 1 =demo.query("select * from EMPLOYEE") 2 =A1.groups(DEPT; sum(SALARY): SalarySum) Group and aggregate 3 =A2.select(SalarySum >200000) Filter the aggregate result 4 =A1.group(DEPT) Group 5 =A4.select(~.count()>30) Filter grouped subsets

## Perform equi-join on same-level tables

 A 1 =demo.query("select * from STATENAME") 2 =demo.query("select * from STATEINFO") 3 =join(A1:StateName,STATEID;A2:StateInfo,STATEID)

## Perform equi-join on main table and subtable

 A 1 =demo.query("select * from STATES") 2 =demo.query("select * from EMPLOYEE") 3 =join(A1:State,NAME;A2:Employee,STATE)

## Non-equi-joins

 A 1 =demo.query("select * from EMPLOYEE") 2 =demo.query("select * from LIQUORS") 3 =demo.query("select * from RECEIPT") 4 =xjoin(A1:Employee,STATE=="New York";A2:Liquor, STOCK>500;A3:Food,QUANTITY>2)

## LEFT JOIN/FULL JOIN

 A 1 =demo.query("select * from EMPLOYEE") 2 =demo.query("select * from STATES") 3 =demo.query("select * from ATTENDANCE") 4 =demo.query("select * from PERFORMANCE") 5 =join@1(A2:State,NAME;A1:Employee,STATE) Left join 6 =join@f(A2:State,NAME;A1:Employee,STATE) Full join 7 =join@1(A1:Employee,EID;A3:Attendance, EMPLOYEEID;A4:Performance, EMPLOYEEID) Join with the first table in alignment