Post reply

Problem with calculation

  • February 7, 2007 at 12:28 am

    #194228

    Well guys and women

    I have a little problem which breaks e really down.

    I have a db europa, with tables: north, middle, east west and balkan.

    In table north I have fields: iceland, norway, sweden, etc.

    In those fields I have diferent number of records: for example

    In field iceland I have 30 records, in norway 56, in sweden 87 etc.

    I made a query like this:

    SELECT Count([north].[iceland]) AS ICE, etc.

    FROM [north];

    How to make a query which sumize all the records in table north?

    I tried

    SELECT (*)

    FROM [north] but it didn't work out.

     

    Please help me

     

  • February 7, 2007 at 9:26 am

    #688320

    Hi,

    I'll try to help but I'm a bit confused as to the setup of your tables. If you could shed some light on the following it would make it easier:

    You say you have x records for Norway and y records for Sweden; if there is a record in Norway is Sweden NULL and vice versa or do both fields contain data? i.e

    Norway  |  Sweden

    -------------------

    data      |  NULL          - A Norway record

    data      |  NULL          - A Norway record

    NULL     |   data          - A Sweden record

    OR

    data      |  data          -  One for Norway one for Sweden?

    If you could post the table structure and some sample data it will be easier to help you.

    Ade


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • February 7, 2007 at 11:32 pm

    #688458

    Well I'll try to explain.

    Data types for all fields are TEXT, 40 (maximum number of characters in each field is 40).

    I have, of course NULL's in both fields, but expression: SELECT COUNT does actually just counting those records which doesn't have NULL's.

    That means if I put some data in 25 records, and leave 15 NULL's, query SELECT COUNT would show number of 25 (I ordered to how just number of filled records, not NULL's).

    Table looks something like this:

    norway                sweden                     finland

    Lilestrem            Malmo                        HJK Helsinki

    Rozenborg         IFK Goteborg               Ilves Tampere

    Bergen, etc        Djurgarden, etc.          RoPS, etc.

     

    I made a query which will show me number of clubs that are in each field.

    My question is how to sum those numbers (in above case that sum will be 9).

    I hope you understand it now. 

     

     

  • February 8, 2007 at 2:08 am

    #688481

    I see. Thanks for the details!

    Something like this should give the answer you're looking for:

    SELECT COUNT(norway) + COUNT(sweden) + COUNT(finland)

    FROM [north]

    Hope this helps!!

    Ade


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply