Solved

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

Posted on 2011-03-15
2
342 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
2 Comments
 
LVL 3

Accepted Solution

by:
ssisworo earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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…

914 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now