Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-15
6
Medium Priority
?
486 Views
Last Modified: 2012-06-27
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
Comment
Question by:parlays
6 Comments
 
LVL 4

Expert Comment

by:qasim_md
ID: 35144257
try this :::
select fruit , COUNT(*) from table
where fruit IN( 'lemon' ,'apple');
group by fruit

did that help
0
 
LVL 8

Assisted Solution

by:kingjely
kingjely earned 664 total points
ID: 35144258
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
 

Author Comment

by:parlays
ID: 35144600
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 41

Accepted Solution

by:
Sharath earned 668 total points
ID: 35144739
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
 
LVL 3

Assisted Solution

by:ssisworo
ssisworo earned 668 total points
ID: 35146493
try this :

SELECT SUM(IF(fruit = 'lemon', 1,0)) as lemon_count, SUM(IF(fruit = 'apple', 1,0)) as apple_count  FROM table
0
 

Author Comment

by:parlays
ID: 35150999
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

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question