1

I'm trying to write an SQL query that will output multiple variables depending on how many times a specific quantity exists in the DB. This information will then be used in a chart.

The DB is set as following:

ID | quantity | etc..
---------------------
1  |   2      |
2  |   6      |
3  |   1      |
4  |   2      |
.
.
.

Here is the query I made:

SELECT 
  COUNT(CASE WHEN quantity = 1) AS quantity1, 
  COUNT(CASE WHEN quantity = 2) AS quantity2
FROM orders

But I want it to output like this:

quantity1 | quantity2
---------------------
    1     |   2      |

Instead I am getting these error messages:

enter image description here

Thanks in advance!

EDIT: If you want to test your code on my database, the link is below...

-Link Removed-

user3684557
  • 73
  • 1
  • 10

2 Answers2

3

Here is another approach, maybe it helps:

SELECT SUM(CASE WHEN quantity = 1 THEN 1 ELSE 0 END) AS quantity_1,
       SUM(CASE WHEN quantity = 2 THEN 1 ELSE 0 END) AS quantity_2,
       SUM(CASE WHEN quantity = 3 THEN 1 ELSE 0 END) AS quantity_3,
       SUM(CASE WHEN quantity = 4 THEN 1 ELSE 0 END) AS quantity_4,
       SUM(CASE WHEN quantity = 5 THEN 1 ELSE 0 END) AS quantity_5,
       SUM(CASE WHEN quantity = 6 THEN 1 ELSE 0 END) AS quantity_6,
       SUM(CASE WHEN quantity > 6 THEN 1 ELSE 0 END) AS quantity_more_than_6
  FROM orders

Here is a special query for MS Access:

SELECT SUM(SWITCH(quantity = 1, 1)) AS quantity_1,
       SUM(SWITCH(quantity = 2, 1)) AS quantity_2,
       SUM(SWITCH(quantity = 3, 1)) AS quantity_3,
       SUM(SWITCH(quantity = 4, 1)) AS quantity_4,
       SUM(SWITCH(quantity = 5, 1)) AS quantity_5,
       SUM(SWITCH(quantity = 6, 1)) AS quantity_6,
       SUM(SWITCH(quantity > 6, 1)) AS quantity_more_than_6
  FROM orders
DirkNM
  • 2,614
  • 15
  • 21
  • I get the same error as in OP, but that looks like its on the right track. IErrorInfo.GetDescription failed.... what does this meeean?? – user3684557 Oct 20 '14 at 09:44
  • I have added the database file to the OP – user3684557 Oct 20 '14 at 10:38
  • Looks like you use MS Access, it that right? I updated my answer for that case. – DirkNM Oct 20 '14 at 10:50
  • YES YES YES YES!!!! I didn't know there was a difference. Thank you so much! So for future reference what/how did you get to this? – user3684557 Oct 20 '14 at 10:56
  • I just googled for "ms access sql case syntax" and the first hit points to http://stackoverflow.com/questions/14920116/does-ms-access-support-case-when-clause-if-connect-with-odbc – DirkNM Oct 20 '14 at 10:59
0

Use a GROUP BY clause , something like :

SELECT quantity, COUNT(*) AS num
FROM Orders
GROUP BY quantity
HAVING COUNT(*) > 6;

EDIT: You can add a HAVING clause

CocoNess
  • 4,213
  • 4
  • 26
  • 43