Solved

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

Posted on 2011-03-15
2
348 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 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo‚Ķ
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

717 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