Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

Is it possible to do a multi column count in a SQL query?

I have the following data in a table:

+----+------------+-----------+------------+
|  id  |   fruit      | vegetable | date       |
+----+------------+-----------+------------+
|  1 | lemon      | lettuce   | 2011-01-10 |
|  2 | orange     | parsley   | 2011-01-10 |
|  3 | lemon      | parsley   | 2011-01-10 |
|  4 | watermelon | apple     | 2011-01-10 |
|  5 | lemon      | cucumber  | 2011-01-10 |
|  6 | lemon      | apple     | 2011-01-10 |
|  7 | orange     | apple     | 2011-01-10 |
|  8 | apple      | lettuce   | 2011-01-11 |
|  9 | lemon      | lettuce   | 2011-01-11 |
| 10 | orange     | parsley   | 2011-01-11 |
+----+------------+-----------+------------+

I want to be able to combine the results of two queries in one, here are the two queries:
1. SELECT COUNT(*) as lemon_count FROM table where fruit = 'lemon';
2. SELECT COUNT(*) as apple_count FROM table where vegetable = 'apple';

I'd like to do something like
SELECT COUNT(*) as lemon_count, COUNT(*) as apple_count FROM table where fruit = 'lemon' or vegetable = 'apple';

Basically a way to get both counts in one query.  Is that possible?
0
parlays
Asked:
parlays
3 Solutions
 
qasim_mdCommented:
try this :::
select fruit , COUNT(*) from table
where fruit IN( 'lemon' ,'apple');
group by fruit

did that help
0
 
kingjelyCommented:
Hi,
There are a few ways, here is one

SELECT    (select count(fruit) FROM table where fruit = 'lemon') as lemon_count,
                 (select count(vegetable) FROM table where vegetable = 'apple') as apple_count
from table
group by lemon_count, apple_count;
0
 
parlaysAuthor Commented:
Thanks Kingjely, would you mind showing me the other options.

Also, is there a way to also get the total of both returned as well (just curious, don't need to answer).  thanks!!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SharathData EngineerCommented:
try this.
SELECT SUM(CASE 
             WHEN fruit = 'lemon' THEN 1 
             ELSE 0 
           END) AS lemon_count, 
       SUM(CASE 
             WHEN vegetable = 'apple' THEN 1 
             ELSE 0 
           END) AS apple_count 
  FROM your_table

Open in new window

0
 
ssisworoCommented:
try this :

SELECT SUM(IF(fruit = 'lemon', 1,0)) as lemon_count, SUM(IF(fruit = 'apple', 1,0)) as apple_count  FROM table
0
 
parlaysAuthor Commented:
Thanks everyone for the help, greatly appreciate it!!

qasim_md, that query didn't do what I wanted but it is partly my fault for using a confusing example where I have apple listed as a vegetable, lol, but thanks for the response!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now