?
Solved

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

Posted on 2011-03-15
2
Medium Priority
?
350 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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 Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

741 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