By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,453 Members | 937 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,453 IT Pros & Developers. It's quick & easy.

Sql count query

P: 11
Hi all

I need help in dis, i m working on a project and here i have to design a query and it has some values(say Student ID) in columns which is repeating. So i want all the columns in output and also the number of times that Student ID is same in every record corrosponding to that student ID.......plz help itz urgent....!!!
Jun 28 '10 #1
Share this Question
Share on Google+
6 Replies


code green
Expert 100+
P: 1,726
Expand|Select|Wrap|Line Numbers
  1. COUNT(student ID) GROUP BY student ID
Jun 28 '10 #2

P: 11
hey thanx for reply but that is not enough i want all the other columns in my output too and also this count

eg
student id

A
B
C
A
B
A

then i m expecting output to be

Student id
count
A 3
B 2
C 1
A 3
B 2
A 3
Jun 28 '10 #3

code green
Expert 100+
P: 1,726
i want all the other columns in my output too
Then put the other columns in.
Do you get the correct result without the GROUP BY?
Jun 28 '10 #4

P: 11
SELECT customer_name, COUNT(city) as "Distinct Cities"
FROM customers
GROUP BY customer_name;



i tried using this query and it worked but it is giving me the unique values only i.e. it is not repeating them as i want them..... and also in case i SELECT all the other fields it again gives error...

also as u said if i wont use GROUP BY then it will just give me a total number(of STUDENT/CUSTOMER ID) dat is of no use for me
Jun 28 '10 #5

P: 6
@Anubhav Mital
you need a compound query

the easiest is to save your count query (don't use spaces in field names like DistinctCities) as a separate query called CityCount (or similar)

SELECT customer_name, COUNT(city) as "DistinctCities"
FROM customers
GROUP BY customer_name;

then create another query like this

SELECT customers.*, CityCount.DistinctCities from customers INNER JOIN CityCount ON customers.customer_name = CityCount.customer_name;

(not tested; I may have made a typo)

Richard "Manxman" Killey
Jun 28 '10 #6

P: 11
KK thanx buddy ....i also found the similar solution to it but i hadnt implemented it yet.. i hope it will work thanx for ur help...i case of any queries i will disturb u again..:)
Jun 28 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.