Example Queries - Part 2

Paged Query

`SELECT id, name, address, phone FROM customers ORDER BY name LIMIT 10 OFFSET 990

Aggregates of specified size

Find the values of a table column c1 for which there are a specified number of listed values in another column c2.

To get an overview of the values of c2 for each value of c1:

SELECT   c1,  GROUP_CONCAT(c2 ORDER BY c2) AS 'C2 values'
FROM table GROUP BY c1;

To retrieve a list of c1 values for which there exist specific values in another column c2, you need an IN clause specifying the c2 values and a HAVING clause specifying the required number of different items in the list …

SELECT c1 FROM table WHERE c2 IN (1,2,3,4) GROUP BY c1 HAVING COUNT(DISTINCT c2)=4;

This is easy to generalise to multiple column expressions, and a HAVING clause specifying any number of items from the IN list.

Queries and Conditions

To obtain part sums of a column based on specific conditions …
select sum( if(condition1, col, 0) ) as cond1, sum( if(condition2, col, 0) ) as cond2, from your_table_name;