0

Let's say I have a table like below

uid      | value      | date_added
-----------------------------------
14152    | NA4156     | 2019-03-01
14153    | EA4656     | 2019-03-02
14152    | EA4656     | 2019-03-02
14155    | CA4456     | 2019-03-03
14153    | DA4556     | 2019-03-03
14157    | EA4656     | 2019-03-03
14155    | FA4756     | 2019-03-06
14159    | GA4856     | 2019-03-06

If I would like to return all data with an count based on uid

uid      | value      | count
-----------------------------------
14152    | NA4156     | 2
14153    | EA4656     | 3
14152    | EA4656     | 2
14155    | CA4456     | 2
14153    | DA4556     | 3
14153    | EA4656     | 3
14155    | FA4756     | 2
14159    | GA4856     | 1

As a limited sql user, I cannot figure out how. Thanks in advance.

1 Answer 1

0

Your example data doesn't correspond with your reszkt. But you are looking for a INNER JOIN with a subquery

CREATE TABLE table1 (
  `uid` INTEGER,
  `value` VARCHAR(6),
  `date_added` VARCHAR(10)
);

INSERT INTO table1
  (`uid`, `value`, `date_added`)
VALUES
  ('14152', 'NA4156', '2019-03-01'),
  ('14153', 'EA4656', '2019-03-02'),
  ('14152', 'EA4656', '2019-03-02'),
  ('14155', 'CA4456', '2019-03-03'),
  ('14153', 'DA4556', '2019-03-03'),
  ('14157', 'EA4656', '2019-03-03'),
  ('14155', 'FA4756', '2019-03-06'),
  ('14159', 'GA4856', '2019-03-06');
✓

✓
SELECT
t1.`uid`, t1.`value`, total_count
FROM
table1 t1 InnER JOIN (SELECT Count(*) total_count, `uid` FROM table1 GROUP BY `uid`) t2
ON t1.`uid` = t2.`uid`
  uid | value  | total_count
----: | :----- | ----------:
14152 | NA4156 |           2
14153 | EA4656 |           2
14152 | EA4656 |           2
14155 | CA4456 |           2
14153 | DA4556 |           2
14157 | EA4656 |           1
14155 | FA4756 |           2
14159 | GA4856 |           1

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.