Thread: how to do this

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    34
    Rep Power
    12

    how to do this


    i have this problem.

    I have 3 columns in the table and each of these is filled with integers from 1 to 8.In one row can be one number just once and same integers in different rows can be in different columns.for example: 1st row: 1,5,3 2nd row:3,4,5 ....
    What i want to do is to get an result which will tell me how many rows contains 1,in how many rows is 2, ......8

    Is it possible to do it with one select+count(*)+groupBY, or what are the possibilities.

    Thank you very much
  2. #2
  3. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Hmmm, from your initial description it sounds like the data might not be normalized. Can you provide a little bit more info about your db/table structure?

    Some intial brainstorming thoughts:

    *Not sure if this is feasible, but perhaps a complicated series of CASE statements to try and sort the data

    *Try breaking up your table into 2 tables where the integers reference your main table via a foreign key.

    HTH,
    -b
    PostgreSQL, it's what's for dinner...
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    34
    Rep Power
    12
    ok, i will describe the situation to make it more clear:

    i have these columns in the table : id_fac (id of accomodation facility (integer)), type_fac (type of facility (integer 1..5)), loc_1(location of facility 1(integer 1..8)), loc_2(location of facility 2(integer 1..8)), loc_3(location of facility 3(integer 1..8)).

    One facility can be located in three defferent geographical areas, which are identificated by columns loc_1,loc_2,loc3.In one row, there can be one location used just once.But for ex. area no.5 can be in loc_1 for one facility, but for another , it can be in loc_3, so rows of the table can look like this:

    1.st row: 011123,4,2,5,8
    2.st row: 011124,1,3,2,5
    3.st row: 011125,2,4,7,2
    .
    .
    .

    I'will do some searching with this table , for ex. i will look for all facilities which are type 4, or for all which are located in area 3....

    Before getting results i want to get some statistic which will tell me how many facilities of type TYPE are in area no.1 ,no2,no.3,...

    so if my searching will be done without any WHERE conditio, statistic resulwill look like:

    fac type 1: area 1: 23,area 2:45,area 3:89,...
    fac type 2: area 1: 75,area 2:54,area 3:93,...
    fac type 3: area 1: 29,area 2:42,area 3:19,...
    .
    .

    if i will want just facs of type 5:

    fac type 5: area 1: 29,area 2:42,area 3:19,...

    and so on


    thanx for any suggestions
  6. #4
  7. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    It sounds like you'll make things a lot easier if you break up your repeating columns into a separate table.

    What I would do is remove loc_1, loc_2, and loc_3 from your table (for example, facility_location), and create a new table with 2 columns:
    a foreign key to facility_id and then location_id with a composite primary key of facility_id and location_id.

    To get the stats for amount of types of facilities at a given location do something like*note I did not explicitly test this query, but it should give you something to go off of):

    SELECT count(*), type_fac, location_id FROM original_table, facility_location GROUP BY facility_location.location_id, type_fac

    AND to get all ones with type 4 at location 3 you should be able to just do a join across the tables.

    HTH,
    -b
    PostgreSQL, it's what's for dinner...

IMN logo majestic logo threadwatch logo seochat tools logo