Skip to content

Instantly share code, notes, and snippets.

@blockjon

blockjon/gist:5662740

Last active December 17, 2015 19:49
Show Gist options
  • Save blockjon/5662740 to your computer and use it in GitHub Desktop.
Save blockjon/5662740 to your computer and use it in GitHub Desktop.
Download ZIP
What sql query will produce the results set below using just a single table scan?
Raw
gistfile1.txt
What sql query will produce the results set below using just a single table scan?
animals
-----------------
| size | color |
-----------------
| big | red |
| small | red |
| small | green |
-----------------
Results set:
-----------------
| big | red |
-----------------
| 1 | 2 |
-----------------
@smacphee
Copy link

select top 1 1 as big, 2 as red from animals

@morellan
Copy link 

SELECT
  SUM(IF(size='big', 1, 0)) AS big,
  SUM(IF(color='red', 1, 0)) AS red
FROM animals

quick & dirty

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment