Easiest Way to Get a Total Count and a Count of a Subset

Easiest way to get a total count and a count of a subset?

You could use a CTE:

WITH T1 AS (
    SELECT DATEPART(WEEKDAY,start_date) AS weekday
    FROM attend
    WHERE empl_no = 12345
)
SELECT
    (SELECT COUNT(*) FROM T1) AS total,
    (SELECT COUNT(*) FROM T1 WHERE weekday = 2 OR weekday = 6) AS subset

How can I get both the count of a subset as well as the count of the total set in one query?

If enabled is always 1 or 0, you can do:

SELECT 
   COUNT(*) as total_count,
   SUM(enabled) as enabled_count
 FROM widget

If it's another value, perhaps:

SELECT
   COUNT(*) as total_count,
   SUM( CASE WHEN enabled in ('enabled value 1', 'enabled value 2') 
        THEN 1
        ELSE 0
        END
      ) as enabled_count
FROM widget

how to get total and total of subset using mysql?

Maybe this is what you want:

SELECT 
  department, 
  SUM(type LIKE '%robot%') robots, 
  COUNT(department) total, 
  SUM(type LIKE '%robot%') / COUNT(department) robot_percentage
FROM agents
GROUP BY department;

Example SQL Fiddle

To format the robot_percentage with percent sign you coud do:

CONCAT(FORMAT( (SUM(type LIKE '%robot%')*100 / COUNT(department)),2),'%') robot_percentage

total count and subset count inside a group by

One way to do it is using conditional aggregation:

Create and populate sample table (Please save us this step in your future questions)

CREATE TABLE trip 
(
    id int, 
    start_date datetime,
    end_date datetime
)

INSERT INTO trip VALUES
(7454, '2013-09-01 01:01:00.000', '2013-09-01 01:05:00.000'),
(7457, '2013-09-01 01:09:00.000', '2013-09-01 01:12:00.000'),
(7458, '2013-09-01 02:01:00.000', '2013-09-01 02:08:00.000'),
(7459, '2013-09-01 02:04:00.000', '2013-09-01 02:23:00.000'),
(7460, '2013-09-01 02:04:00.000', '2013-09-01 02:25:00.000'),
(7461, '2013-09-01 02:09:00.000', '2013-09-01 02:12:00.000'),
(7463, '2013-09-01 02:19:00.000', '2013-09-01 02:29:00.000'),
(7465, '2013-09-01 02:27:00.000', '2013-09-01 02:29:00.000'),
(7466, '2013-09-01 04:06:00.000', '2013-09-02 15:08:00.000'),
(7467, '2013-09-01 05:24:00.000', '2013-09-02 05:37:00.000')

The query:

SELECT  DATEADD(MONTH,DATEDIFF(MONTH,0,[start_date]),0) As TripMonth,
        COUNT(id) As 'NumTrips',
        SUM
        (
            CASE WHEN DATEDIFF(DAY, Start_Date, End_Date) > 0 THEN 1 ELSE 0 END
        ) As 'NumTrips>Day'
FROM Trip
GROUP BY DATEADD(MONTH,DATEDIFF(MONTH,0,[start_date]),0)

Results:

TripMonth               NumTrips    NumTrips>Day
01.09.2013 00:00:00     10          2

efficiently find subset of records as well as total count

I'm writing this on top of my head, so you should definitely have to time this, but I believe that using following CTE

• only requires you to write the conditions once
• only returns the amount of records you specify
• has the correct total count added to each record
• and is evaluated only once

SQL Statement

WITH q AS (
  SELECT record
  FROM   table
  WHERE  criteria like :criteria
)
SELECT q1.*, q2.*
FROM   q q1
       CROSS JOIN (
         SELECT COUNT(*) FROM q
       ) q2
WHERE  rownum <= :desiredCount

Getting a count of a subset of GROUP BY records

In SQL Server, you can use conditional aggregation. I typically do this using SUM(CASE . . . ):

SELECT COUNT(*) as TotalMailed,
       SUM(CASE WHEN Returned = 1 THEN 1 ELSE 0 END) as ReturnedMailed,
       Order_No, Zip_Code, County
FROM MailLedger
GROUP BY Order_No, Zip_Code, County;

If Returned only takes on the values of 1 and 0/NULL, then you can do:

SELECT COUNT(*) as TotalMailed,
       SUM(Returned) as ReturnedMailed,
       Order_No, Zip_Code, County
FROM MailLedger
GROUP BY Order_No, Zip_Code, County;

Getting a Subset of Records along with Total Record Count

Here is what I have done (and its just as fast, no matter which records I return):

--Parameters include:
@pageNum int = 1,
@pageSize int = 0,

DECLARE 
    @pageStart int,
    @pageEnd int

SELECT
    @pageStart = @pageSize * @pageNum - (@pageSize - 1),
    @pageEnd = @pageSize * @pageNum;

SET NOCOUNT ON;
WITH tempTable AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY FirstName ASC) AS RowNumber,
        FirstName
        , LastName
    FROM People
    WHERE Active = 1
)

SELECT
    (SELECT COUNT(*) FROM tempTable) AS TotalRows,
    *
FROM tempTable
WHERE @pageEnd = 0
OR RowNumber BETWEEN @pageStart AND @pageEnd
ORDER BY RowNumber

COUNTing total rows, whilst simultaneously SELECTing a subset of these - in one MySQL query?

This should give you what you're hoping for:

SELECT reference.id, reference.name, COUNT(1) AS complete_count
FROM reference
WHERE status = 2
   OR status = 3
GROUP BY reference.id, reference.name
ORDER BY reference.date
LIMIT 5

---

Related Topics