Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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!
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page