Counting C/Rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Counting C/Rows

Author

Topic

urmas80
Starting Member

20 Posts

Posted - 2012-07-09 : 07:24:33

HI all,

It's should be basic for you, but not for me...

I have table with a column of repeating values and i want to count the number of repeating values for each type of value:

Values in column
A
A
B
B
B
C
D
D
D
E
E
E
E

Expected:
Value Count
A 2
B 3
C 1
D 3
E 4

Thank you in advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-09 : 07:27:18

[code]SELECT
Col1,
COUNT(*) as [Count]
FROM
YourTable
GROUP BY
Col1[/code]

urmas80
Starting Member

20 Posts

Posted - 2012-07-10 : 15:35:26

Great solution, thank you!

Now additional question how i create table of value's that replicated 3 times.

Expected:
Values
B
D

all that in one phrase

thank you in advance

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-10 : 15:39:20

[code]SELECT
Col1,
COUNT(*) as [Count]
FROM
YourTable
GROUP BY
Col1
HAVING
COUNT(*)=3[/code]

urmas80
Starting Member

20 Posts

Posted - 2012-07-10 : 15:46:05

so simple... I am Noob!!

urmas80
Starting Member

20 Posts

Posted - 2012-07-11 : 18:29:42

I got an Exercise that i am working on for a few days and i just can't figure it out, i hoped to find solution from your answers but it's keeps getting away from me...

Here is the exercise:

I got table:

Product(maker, model, type)

maker model type
A 1232 PC
A 1233 PC
A 1276 Printer
A 1298 Laptop
A 1401 Printer
A 1408 Printer
A 1752 Laptop
B 1121 PC
B 1750 Laptop
C 1321 Laptop
D 1288 Printer
D 1433 Printer
E 1260 PC
E 1434 Printer
E 2112 PC
E 2113 PC

Find out makers who produce only the models of the same type, and the number of those models exceeds 1.
Deduce: maker, type

urmas80
Starting Member

20 Posts

Posted - 2012-07-12 : 07:15:09

*bump

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-12 : 07:22:31

I am reluctant to post anything because you said "exercise"; Moderators of this forum do not look kindly upon people giving away answers to students.

I would think about it in this terms:
a) I need to get the Makers. So that is a "SELECT Maker FROM Product"
b) I need to get makers who make only one product type. So that is a HAVING clause - "HAVING COUNT (Distinct type) = 1
c) I need to get makers who make more than one model. That would again be another similar condition in the HAVING clause.
d) Since you are using an aggregate function, you will need a GROUP by clause.

If I say anything more, I may get banned from this site

urmas80
Starting Member

20 Posts

Posted - 2012-07-12 : 09:59:05

don't worry regarding my study, i can promise you i am not a student of any official academy, i am study on my own from online exercises, and that's why i has some huge holes in my understanding of the SQL.

from what i understand, the dependency of the answer is between model and type, because the answer should be:

maker type
D Printer

so first of all i need to to build a condition that checks for which maker i have only one type of hardware. so that answer will be

maker type
D Printer
C Laptop

lest start with that, then i will try on my own...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-12 : 10:20:31

quote:
Originally posted by urmas80

don't worry regarding my study, i can promise you i am not a student of any official academy, i am study on my own from online exercises, and that's why i has some huge holes in my understanding of the SQL.

from what i understand, the dependency of the answer is between model and type, because the answer should be:

maker type
D Printer

so first of all i need to to build a condition that checks for which maker i have only one type of hardware. so that answer will be

maker type
D Printer
C Laptop

lest start with that, then i will try on my own...

look for GROUP BY,COUNT and HAVING clauses in books online

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources