#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2014
    Posts
    14
    Rep Power
    0

    Counting of distinct values


    Hello everyone,

    I have a table which among others contains the following columns:
    id (primary key)
    foreign_id
    date
    status

    I use:

    SELECT * FROM `table` WHERE `date` >='2017-01-01'

    I get a list of entries from this year. Now I want the list to display one row for each distinct foreign_id with a column for each distinct status and the count of these occurences.

    Example (subset of foreign_id 103 and 104):
    Code:
    id	foreign_id	date	status
    4457	103	10.10.2016	7
    4458	103	10.10.2016	4
    4459	103	10.10.2016	4
    4460	103	10.10.2016	4
    4461	103	10.10.2016	4
    4462	103	10.10.2016	6
    4463	103	10.10.2016	7
    4464	104	10.10.2016	4
    4465	104	10.10.2016	4
    4466	104	10.10.2016	4
    4467	104	10.10.2016	7
    4468	104	10.10.2016	4
    4469	104	10.10.2016	6
    4470	104	10.10.2016	4
    4471	104	10.10.2016	6
    4472	104	10.10.2016	4
    4473	104	10.10.2016	7
    4474	104	10.10.2016	6
    4475	104	10.10.2016	6
    4476	104	10.10.2016	6
    4477	104	10.10.2016	4
    would show as:
    foreign_id 4 5 6 7
    103 4 0 1 2
    104 7 0 5 2

    I tried:

    Code:
    SELECT DISTINCT `foreign_id`, (SELECT COUNT(`status`) FROM `table` WHERE `status`=4)AS `4` 
    FROM `table` 
    WHERE `date` >= '2017-01-01'
    GROUP BY `foreign_id`
    but it fails in the nested query where I would have to specify the current foreign_id too. What am I missing?
    Thanks for any help!
    J.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,690
    Rep Power
    4288
    Code:
    SELECT `foreign_id`
         , COUNT(CASE WHEN `status` = 4 THEN 'ok' ELSE NULL END) AS '4'
         , COUNT(CASE WHEN `status` = 5 THEN 'ok' ELSE NULL END) AS '5'
         , COUNT(CASE WHEN `status` = 6 THEN 'ok' ELSE NULL END) AS '6'
         , COUNT(CASE WHEN `status` = 7 THEN 'ok' ELSE NULL END) AS '7'
      FROM `table` 
     WHERE `date` >= '2017-01-01'
    GROUP 
        BY `foreign_id`
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo