Counting of clients-CodePudding

I have a table and a list of client with respective id which transact to an item and I want them to count how many of that were my new client within the year and how many transaction per customer.

    <table>
    <thead>
    <tr>
    <th>UniqueID</th>
    <th>Date</th>
    <th>Country</th>
    <th>ReceiptNo</th>
    </tr>
    </thead>
    <tbody>
    <tr>
    <td>CDR100011</td>
    <td>04/15/2015</td>
    <td>CHINA</td>
    <td>495651633164117</td>
    </tr>
    <tr>
    <td>CDR100012</td>
    <td>5/16/2014</td>
    <td>SINGAPORE</td>
    <td>902549556061179</td>
    </tr>
    <tr>
    <td>CDR100013
    </td>
    <td>8/2/2019</td>
    <td>THAILAND
    </td>
    <td>250749065260758
    </td>
    </tr>
    <tr>
    <td>CDR100013
    </td>
    <td>10/2/2015</td>
    <td>THAILAND
    </td>
    <td>554626060959714
    </td>
    </tr>
    <tr>
    <td>CDR100015

    </td>
    <td>8/28/2016</td>
    <td>AUSTRALIA

    </td>
    <td>171768803140265

    </td>
    </tr>
    <tr>
    <td>CDR100016

    </td>
    <td>3/17/2021
</td>
    <td>TAIWAN

    </td>
    <td>820516048850062

    </td>
    </tr>
    <tr>
    <td>CDR100017

    </td>
    <td>4/2/2016</td>
    <td>SINGAPORE

    </td>
    <td>218012057641382

    </td>
    </tr>
    <tr>
    <td>CDR100017

    </td>
    <td>7/14/2021
</td>
    <td>SINGAPORE

    </td>
    <td>999105086662729

    </td>
    </tr>
    <tr>
    <td>CDR100019

    </td>
    <td>6/25/2020
</td>
    <td>TAIWAN

    </td>
    <td>974479049682501

    </td>
    </tr>
    <tr>
    <td>CDR100020

    </td>
    <td>4/21/2020
</td>
    <td>CHINA

    </td>
    <td>668185025024787
    </td>

    </tr><tr>
    <td>CDR100021

    </td>
    <td>7/5/2020
</td>
    <td>MALAYSIA

    </td>
    <td>134049410374093

    </td>
    </tr><tr>
   

    </tbody>
    </table>

Sample output: (don't count if id already exist)

Year Client
2014 1
2015 2
2016 2
2019 0 
2020 3
2021 1

Select count(uniqueid)?

CodePudding user response：

I think you are looking for this;

SELECT extractedYear, COUNT(*) AS CLIENT FROM (SELECT EXTRACT(YEAR FROM MIN(t.date)) AS extractedYear, t.UniqueID FROM transactions t GROUP BY t.UniqueID) a GROUP BY extractedYear ;

CodePudding user response：

that is no vaild mysql date, so please save dates always in mysql format yyyy-mm-dd.

Another problem, is that mysql has no possiblity to make easy time table, so you need tojoin the years as separate table

CREATE TABLE tansac (
  `UniqueID` VARCHAR(9),
  `Date` DATETIME,
  `Country` VARCHAR(9),
  `ReceiptNo` BIGINT
);

INSERT INTO tansac
  (`UniqueID`, `Date`, `Country`, `ReceiptNo`)
VALUES
  ('CDR100011', STR_TO_DATE('04/15/2015','%m/%d/%Y'), 'CHINA', '495651633164117'),
  ('CDR100012', STR_TO_DATE('5/16/2014','%m/%d/%Y'), 'SINGAPORE', '902549556061179'),
  ('CDR100013', STR_TO_DATE('8/2/2019','%m/%d/%Y'), 'THAILAND', '250749065260758'),
  ('CDR100013', STR_TO_DATE('10/2/2015','%m/%d/%Y'), 'THAILAND', '554626060959714'),
  ('CDR100015', STR_TO_DATE('8/28/2016','%m/%d/%Y'), 'AUSTRALIA', '171768803140265'),
  ('CDR100016', STR_TO_DATE('3/17/2021','%m/%d/%Y'), 'TAIWAN', '820516048850062');

SELECT t2.year_a as 'Year',IFNULL(trancations,0) as 'trancations'
FROM
(SELECT 2014 year_a
            UNION SELECT 2015 
            UNION SELECT 2016 
            UNION SELECT 2017 
            UNION SELECT 2018 
            UNION SELECT 2019
            UNION SELECT 2020
            UNION SELECT 2021) t2
LEFT JOIN (SELECT YEAR(`date`) as 'year', IFNULL(COUNT(*),0) as 'trancations'
FROM tansac t1
GROUP BY 1) t1   ON t1.`year` = t2.year_a
ORDER BY 1

Year | trancations
---: | ----------:
2014 |           1
2015 |           2
2016 |           1
2017 |           0
2018 |           0
2019 |           1
2020 |           0
2021 |           1

db<>fiddle here