| Author |
 Topic  |
|
|
urmas80
Starting Member
13 Posts |
 Posted - 07/09/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/09/2012 : 07:27:18
|
SELECT
Col1,
COUNT(*) as [Count]
FROM
YourTable
GROUP BY
Col1 |
 |
|
|
urmas80
Starting Member
13 Posts |
Posted - 07/10/2012 : 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
USA
15559 Posts |
Posted - 07/10/2012 : 15:39:20
|
SELECT
Col1,
COUNT(*) as [Count]
FROM
YourTable
GROUP BY
Col1
HAVING
COUNT(*)=3 |
|
|
|
urmas80
Starting Member
13 Posts |
Posted - 07/10/2012 : 15:46:05
|
| so simple... I am Noob!! |
|
|
|
urmas80
Starting Member
13 Posts |
Posted - 07/11/2012 : 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
13 Posts |
Posted - 07/12/2012 : 07:15:09
|
*bump  |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/12/2012 : 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
13 Posts |
Posted - 07/12/2012 : 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
India
47099 Posts |
Posted - 07/12/2012 : 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/
|
|
|
| |
Topic |
|
Counting C/Rows
