how get tihs query for this table(The record run)

Question

1.00/5 (1 vote)

See more:

hi for exmple this table

HTML

id        name       code     
 1        test       20
 2        test2      30  
 3        test       20
 4        test3      40
 5        test       20
 6        test       20

i want this result 
 id       name      code       count
  1       test       20          4

Posted 28-Jan-15 7:44am

saeed1364

Updated 28-Jan-15 7:45am

v2

Add a Solution

Comments

Kornfeld Eliyahu Peter 28-Jan-15 14:09pm

Look for group by...

3 solutions

Solution 1

Use the group by and COUNT.

SQL

SELECT MIN(id) AS id, name, code, COUNT(*) AS count_of_name
FROM table
GROUP BY id, name, code

Richard brought up a good point that I overlooked. You cannot have the ID in your result set if you want the count because you are counting name and code, not id, name, and code.

I updated the sql so that you'll get the lowest ID for each name and code combination.

Update:

Kornfeld also brings up a good point. Even though you put ID in the result set, there's a good chance you don't actually want that in your result set and it's just the numbering of rows in the UI.

Then just remove ID from the sql and you'll have:

SQL

SELECT name, code, COUNT(*) AS count_of_name
FROM table
GROUP BY name, code

But for us to know for sure, we need to hear some feedback from you.

Posted 28-Jan-15 8:09am

ZurdoDev

Updated 28-Jan-15 8:40am

v3

Comments

Richard Deeming 28-Jan-15 14:21pm

Based on the question, I suspect the OP wants to group by name and code, and take the minimum id.

He possibly also wants to filter the results to those records with more than one entry, since "test2" and "test3" don't appear in the result.

ZurdoDev 28-Jan-15 14:31pm

Good call. I didn't pay close attention to the ID. I'll update solution.

Kornfeld Eliyahu Peter 28-Jan-15 14:32pm

Sorry, but wrong answer...
It will give you 4 rows as id can not be grouped (all different values)...
I think new id has nothing to do with old id - it's a simple row-counter...

ZurdoDev 28-Jan-15 14:34pm

Probably right. Richard just pointed that out. I had overlooked it. I updated solution.

Kornfeld Eliyahu Peter 28-Jan-15 14:37pm

Perfect!

ZurdoDev 28-Jan-15 14:41pm

Thanks. I updated AGAIN. It all depends on what user actually wants and without feedback, it's still somewhat of a guess. :(

Kornfeld Eliyahu Peter 28-Jan-15 14:44pm

I think it is an almost perfect answer now to totally imperfect question...
(By the way - it is Peter in most cases and not Kornfeld :-))

ZurdoDev 28-Jan-15 14:46pm

Peter is easier. Thanks. :)

saeed1364 29-Jan-15 0:50am

i want
A record that has a maximum repetition
id is auto number
iam wriet this code

SELECT code, COUNT (code)
FROM table GROUP BY code
HAVING COUNT (code)=
(
SELECT MAX (mycount)
FROM (
SELECT code, COUNT (code) mycount
FROM table
GROUP BY code
)tt
)

ZurdoDev 29-Jan-15 7:24am

An easier way is to use TOP 1 and order by the count descending. That way you'll get the highest one.

SELECT TOP 1 code, COUNT(*) AS count_of_code
FROM table
GROUP BY code
ORDER BY COUNT(*) DESC

Solution 2

Hi Here is your query.

SQL

SELECT MIN(id) AS id, name, code, COUNT(*) AS [Count]
FROM MyTable
GROUP BY  name, code

Please mark as answer.

Thanks,
Vipul

Posted 28-Jan-15 19:21pm

Vipul J Patel

Comments

Vipul J Patel 30-Jan-15 6:50am

Why you have given minus rating? The answer is correct as per your question. Please explain.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

saeed1364 · Accepted Answer · 2015-01-29T18:58:00

Solution 3

SQL

iam wriet this code
 

SELECT code, COUNT (code)
FROM table GROUP BY code
HAVING COUNT (code)=
(
SELECT MAX (mycount)
FROM (
SELECT code, COUNT (code) mycount
FROM table
GROUP BY code
)tt
)

Posted 29-Jan-15 18:58pm

saeed1364