Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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