Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

two queries in one?

Discussion in 'Information Technology' started by dan, Jul 28, 2006.

Tags:
  1. dan

    dan
    Expand Collapse
    Guest

    Hi, I have a large table (1400 records) named data and I want to create
    a second table that has a count of each category in that large table.

    To count how many records for Application Systems, I use:

    SELECT COUNT(*) AS ["Application Systems"]
    FROM data
    WHERE Category="Application Systems"

    I want the seconds table to have a count of the amount of records for
    Application Systems, the next column having the count for Database
    Systems, the next for Servers, etc, based on the column category.

    Is it possible to somehow include all of these queries in one, or is
    there a better way to do it?

    Thank you so much!
     
  2. Loading...

    Similar Threads Forum Date
    Importance of Hukamnama - Some Queries Sikh Sikhi Sikhism Jun 14, 2009
    Queries History of Sikhism Oct 26, 2006

  3. ChrisM

    ChrisM
    Expand Collapse
    Guest

    There might be a cleverer way of doing this, but (possibly) the easiest one
    is a query like:

    Select sum(ApplicationSystems) as ApplicationSystemCount, sum(Dbs) as
    DatabaseSystemCount, sum(Serv) as Servers
    FROM
    (
    SELECT COUNT(*) AS ["ApplicationSystems"], 0 as Dbs, 0 as Serv
    FROM data
    WHERE Category="Application Systems"
    UNION ALL
    0 as ["ApplicationSystems"], SELECT COUNT(*) AS as Dbs, 0 as Serv
    FROM data
    WHERE Category="Database Systems"
    UNION ALL
    0 as ["ApplicationSystems"], 0 AS as Dbs, SELECT COUNT(*) as Serv
    FROM data
    WHERE Category="Servers"
    )

    The above was written from memory, and I haven't checked it too carefully,
    but hopefully you get the idea.
    The only problem is that if you add a new catagory, you have to alter this
    query.

    Don't really use them, but you could maybe look at PivotTable Queries also.

    Cheers,

    Chris.

    "dan" <itzdan262@gmail.com> wrote in message
    news:1151670655.483090.100620@h44g2000cwa.googlegroups.com...
    > Hi, I have a large table (1400 records) named data and I want to create
    > a second table that has a count of each category in that large table.
    >
    > To count how many records for Application Systems, I use:
    >
    > SELECT COUNT(*) AS ["Application Systems"]
    > FROM data
    > WHERE Category="Application Systems"
    >
    > I want the seconds table to have a count of the amount of records for
    > Application Systems, the next column having the count for Database
    > Systems, the next for Servers, etc, based on the column category.
    >
    > Is it possible to somehow include all of these queries in one, or is
    > there a better way to do it?
    >
    > Thank you so much!
    >
     
  4. Rob Parker

    Rob Parker
    Expand Collapse
    Guest

    First, you should not create a new table containing data which is based
    solely on the contents of an existing table. You can get that information
    as a query from the existing table whenever you need it.

    Second, a table such as you describe is not a normalised table; your field
    names should not contain data (in this case, the contents of your Category
    field). For reporting purposes, crosstab queries do summarise existing data
    in this fashion; however, they are NOT primary data tables.

    The simplest method of getting the information you want is to use a Totals
    query on your current table:

    SELECT Category, Count(Category) AS CountOfCategory
    FROM data
    GROUP BY Category;

    HTH,

    Rob

    "dan" <itzdan262@gmail.com> wrote in message
    news:1151670655.483090.100620@h44g2000cwa.googlegroups.com...
    > Hi, I have a large table (1400 records) named data and I want to create
    > a second table that has a count of each category in that large table.
    >
    > To count how many records for Application Systems, I use:
    >
    > SELECT COUNT(*) AS ["Application Systems"]
    > FROM data
    > WHERE Category="Application Systems"
    >
    > I want the seconds table to have a count of the amount of records for
    > Application Systems, the next column having the count for Database
    > Systems, the next for Servers, etc, based on the column category.
    >
    > Is it possible to somehow include all of these queries in one, or is
    > there a better way to do it?
    >
    > Thank you so much!
    >
     

Share This Page