Sql count query

[Anubhav Mital]

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....!!!

[code green]

COUNT(student ID) GROUP BY student ID

[Anubhav Mital]

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

[code green]

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?

[Anubhav Mital]

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

[manxman777]

@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

[Anubhav Mital]

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..:)