Solved

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

Posted on 2011-03-15
6
480 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

773 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