[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to get a count and sub-counts from a SQL query?

Posted on 2011-03-15
2
Medium Priority
?
352 Views
Last Modified: 2012-05-11
I have the following table of data:
+--------+------+----+----------+
|      id    | cp   | mk | date       |
+--------+-----+----+------------+
|         1 |    1 | 0    | 2011-01-10 |
|         2 |    1 | 1    | 2011-01-10 |
|         3 |    1 | 0    | 2011-01-10 |
|         4 |    0 | 1    | 2011-01-10 |
|         5 |    1 | 0    | 2011-01-10 |
|         6 |    1 | 1    | 2011-01-10 |
|         7 |    1 | 1    | 2011-01-10 |
+--------+-----+----+----------------+

What I want to do is get a resultset that groups by date (so for this sample I'll get one row back), and gives me the count of records that have cp=1 or mk=1 and also a count of how many records had cp=1 and how many had mk=1.

So I want the results to show me:
count => 7
count(cp=1) => 6
count(mk=1) => 4
date => 2011-01-10

I was hoping that something like this would work:
SELECT COUNT(*), COUNT(cp=1), COUNT(mk=1), date FROM table WHERE cp=1 or mk=1 GROUP BY date;

but the second and third COUNT give me the same as the COUNT(*), it doesn't filter it for just the cp=1 or mk=1.

I then started to think maybe I need to use a subquery that is something like:
SELECT COUNT(*), cp, mk, date FROM table WHERE (cp=1 or mk=1) and date = '2011-01-10' GROUP BY cp, mk, date;
as a subquery and then the outer query will process that data, but not sure.

Appreciate any help, thanks!!
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
2 Comments
 
LVL 3

Accepted Solution

by:
ssisworo earned 2000 total points
ID: 35143842
try this :

SELECT COUNT(1), SUM(cp), SUM(mk), MAX(date) FROM table GROUP BY date;
0
 

Author Closing Comment

by:parlays
ID: 35144041
Thanks, this works great for my example.  What I need it for doesn't use 1 or 0 in the cp and mk columns, but this helps.
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

This article discusses how to implement server side field validation and display customized error messages to the client.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
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 …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

649 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