Solved

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

Posted on 2011-03-15
6
482 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 166 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 41

Accepted Solution

by:
Sharath earned 167 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 167 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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

738 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