Count of distinct values of a table using sql

aggregatecountdistinct

name  number
=============
lin |   8
lin |   8
lin |   8
rob |   8
rob |   8
rob |   8
lin |   8
lin |   41
lin |   41
rob |   41

and I want the result like

name  |   Count of 8 |   count of 41
====================================
lin   |      4       |        2
rob   |      3       |       1

Can anyone help me please?

Best Answer

This is know as conditional counting and can be achieved using a case expression:

select name, 
       count(case when number = 8 then 1 end) as count_of_8,
       count(case when number = 41 then 1 end) as count_of_41
from the_table
group by name
order by name;

Aggregate functions (like count()) ignore NULL values. The case expression inside the count() returns null for those values that we don't want to count, hence only the number of rows containing that value are counted. case when x then y end is equivalent to case when x then y else null end

YOUR QUERY WITH GROUP BY

SELECT
  student.StudentID,
  student.`Name`,
  COUNT(attendance.AttendanceID) AS Total
FROM
  student
LEFT JOIN attendance ON student.StudentID = attendance.StudentID
GROUP BY student.StudentID,student.`Name`;

SAMPLE DATA

DROP DATABASE IF EXISTS alishaikh; CREATE DATABASE alishaikh;
USE alishaikh
CREATE TABLE student
(
  StudentID CHAR(8) NOT NULL,
  Name VARCHAR(40),
  PRIMARY KEY (StudentID)
);
INSERT INTO student (StudentID,Name) VALUES
('k1052280','Ali Shaikh'),('k1052287','McKenzie Roth'),
('k1052288','Dacey Sullivan'),('k1052294','Zelda Cantu'),
('k1052295','Kimberly Melton'),('k1052296','Tatianna Cantrell'),
('k1052297','Morgan Thornton'),('k1052298','Allistair Barlow'),
('k1052299','Troy Fulton');
CREATE TABLE attendance
(
  AttendanceID INT NOT NULL AUTO_INCREMENT,
  StudentID CHAR(8) NOT NULL,
  PRIMARY KEY (AttendanceID),
  KEY (StudentID)
);
INSERT INTO attendance (StudentID) VALUES
('k1052280'),('k1052280'),('k1052287'),('k1052287'),
('k1052288'),('k1052295'),('k1052295'),('k1052295');

SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS alishaikh; CREATE DATABASE alishaikh;
Query OK, 2 rows affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
mysql> USE alishaikh
Database changed
mysql> CREATE TABLE student
    -> (
    ->   StudentID CHAR(8) NOT NULL,
    ->   Name VARCHAR(40),
    ->   PRIMARY KEY (StudentID)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO student (StudentID,Name) VALUES
    -> ('k1052280','Ali Shaikh'),('k1052287','McKenzie Roth'),
    -> ('k1052288','Dacey Sullivan'),('k1052294','Zelda Cantu'),
    -> ('k1052295','Kimberly Melton'),('k1052296','Tatianna Cantrell'),
    -> ('k1052297','Morgan Thornton'),('k1052298','Allistair Barlow'),
    -> ('k1052299','Troy Fulton');
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE attendance
    -> (
    ->   AttendanceID INT NOT NULL AUTO_INCREMENT,
    ->   StudentID CHAR(8) NOT NULL,
    ->   PRIMARY KEY (AttendanceID),
    ->   KEY (StudentID)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO attendance (StudentID) VALUES
    -> ('k1052280'),('k1052280'),('k1052287'),('k1052287'),
    -> ('k1052288'),('k1052295'),('k1052295'),('k1052295');
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

YOUR QUERY WITH GROUP BY EXECUTED

mysql> SELECT
    ->   student.StudentID,
    ->   student.`Name`,
    ->   COUNT(attendance.AttendanceID) AS Total
    -> FROM
    ->   student
    -> LEFT JOIN attendance ON student.StudentID = attendance.StudentID
    -> GROUP BY student.StudentID,student.`Name`;
+-----------+-------------------+-------+
| StudentID | Name              | Total |
+-----------+-------------------+-------+
| k1052280  | Ali Shaikh        |     2 |
| k1052287  | McKenzie Roth     |     2 |
| k1052288  | Dacey Sullivan    |     1 |
| k1052294  | Zelda Cantu       |     0 |
| k1052295  | Kimberly Melton   |     3 |
| k1052296  | Tatianna Cantrell |     0 |
| k1052297  | Morgan Thornton   |     0 |
| k1052298  | Allistair Barlow  |     0 |
| k1052299  | Troy Fulton       |     0 |
+-----------+-------------------+-------+
9 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

Sql-server – Alternative query to avoid count(distinct)

DISTINCT is always a huge overhead. You might try to move the aggregation into a Derived Table (of course this might only help if the optimizer is not smart enough to do that automatically)

select dc.Country,
  fvcd.Visitors
from 
 (
   select 
       fvcd.IdCountry,
       COUNT(DISTINCT fvcd.IdAppInstance) AS Visitors
   FROM 
       dbo.Fact_VisitorsCountryDetails fvcd
   WHERE
       fvcd.IdDate BETWEEN @IdMinDate AND @IdMaxDate 
       AND fvcd.IdPartnerid = @IdPartnerId
   GROUP BY
       fvcd.IdCountry
 ) fvcd
INNER JOIN dbo.Dim_Country dc ON dc.Id = fvcd.IdCountry   
ORDER BY
    Visitors DESC;