Basic SQL query

john2007 · January 17th, 2007, 07:59 AM

Hi I am just try to learn SQL could help me solving the below request

If you had a customer table that had the customer names and the state where they live, how would you find the number of customers that live in each state? How would you sort the states in Alphabetical order?

Thanks

gbianchi · January 17th, 2007, 08:51 AM

hi there...

well.. if you are trying to learn i will only point you in the right direction...

to order by column you use "ORDER BY"

and to count the number of ocurrencies you have to investigate about aggregate functions (SUM, COUNT,...) and "GROUP BY".

HTH

Gonzalo

BrianWren · January 18th, 2007, 04:39 PM

When using GROUP BY, any counting, summing, averaging, et al., is started over and a new row is generated for each distinct value.
So if you had

Code:

[u]fldSTATE</u>
  WA
  GA
  OR
  GA
  OR

then, with

Code:

SELECT   fldSTATE, COUNT(fldSTATE)

Code:

GROUP BY fldSTATE;

you would get:

Code:

fldSTATE  COUNT(fldSTATE)
--------  ---------------
  GA           2
  OR           2
  WA           1