Member Avatar for end3r

Hy,

I seek your help in solving the following problem:

I have a select which returns columns like ( without count_same which I need to create by counting the same name):

ID | NAME | no_items | count_same
-----------------------------------------------------------
1 | A | 1 | 2
2 | A | 0 | 2
3 | B | 1 | 5
4 | B | 1 | 5
5 | B | 1 | 5
6 | B | 1 | 5
7 | B | 1 | 5


I'm still in the learning phase of SQL and I do not know how this problem can be solved.

Thanks in advance guys,
Ender

  • 2 Contributors
  • 2 Replies
  • 200 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by end3r

Dani AI

Generated

Good call from — you want a per-row count of how many rows share the same NAME while still keeping each ID and no_items row intact. A GROUP BY would collapse rows, so use a windowed aggregate to attach the group count to every row without losing row-level columns.

SELECT id, name, no_items,
       COUNT(*) OVER (PARTITION BY name) AS count_same
FROM YourTable;

If you cannot or prefer not to use window functions, compute counts in a separate grouped subquery and join the results back to the original table. That preserves all original columns and yields the same per-row count.

SELECT t.id, t.name, t.no_items, c.cnt AS count_same
FROM YourTable AS t
JOIN (
  SELECT name, COUNT(*) AS cnt
  FROM YourTable
  GROUP BY name
) AS c ON c.name = t.name;

Notes and gotchas: decide how to treat NULLs and case differences (NULLs group together; use UPPER()/LOWER() or an explicit COLLATE if you want case-insensitive grouping). COUNT(DISTINCT ...) is not generally available as a window aggregate, so use a grouped subquery when you need distinct counts. For large tables, ensure the column used for partitioning/joining is indexed to improve performance. These choices should cover the common edge cases may run into.

Recommended Answers

All 2 Replies

Member Avatar for Aussie_Here 
select id,name,no_items,count(*)
Member Avatar for end3r 
select id,name,no_items,count(*)

Thanks!

This solves one big part of my problem. I don't know why I didn't think about this in the first place...

So the resolution - case solved.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.