Multiple queries SQL

21 Apr 2006 11:02 AM

Ames111

Hi

my first post!!!

i have an organisation table and i want to do something like this

select count(orgname) as Count1 where orgname like 'A%'
select count(orgname) as Count2 where orgname like 'B%'

but i want it to list every organisation, so one on each row, then the
2nd column show the results of the first select query and the 3rd
column to show the result of the 2nd select query so ill have something
like this:

Orgname1     count1     count2
Orgname2     count1     count2
Orgname3     count1     count2
Orgname4     count1     count2
Orgname5     count1     count2

i know that count1 and count2 will always be the same on each row, but
i have just simplified my query for the sake of this post.

so basically for each organisation in the table, i wanna perform a few
queries that relate to that particular organisation.

any help would be great as im totally stuck

andrew

21 Apr 2006 11:34 AM

Tom Moreau

Your specs are cryptic. Perhaps you want a count, broken down by org? If
so:

select
    Orgname
,    count (*)
from
    MyTable
group by
    Orgname
order by
    Orgname

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..

"Ames111" <andrew.a***@4projects.com> wrote in message
news:1145617354.870638.194390@v46g2000cwv.googlegroups.com...
Hi

my first post!!!

i have an organisation table and i want to do something like this

select count(orgname) as Count1 where orgname like 'A%'
select count(orgname) as Count2 where orgname like 'B%'

but i want it to list every organisation, so one on each row, then the
2nd column show the results of the first select query and the 3rd
column to show the result of the 2nd select query so ill have something
like this:

Orgname1     count1     count2
Orgname2     count1     count2
Orgname3     count1     count2
Orgname4     count1     count2
Orgname5     count1     count2

i know that count1 and count2 will always be the same on each row, but
i have just simplified my query for the sake of this post.

so basically for each organisation in the table, i wanna perform a few
queries that relate to that particular organisation.

any help would be great as im totally stuck

andrew

21 Apr 2006 11:40 AM

David Portas

Ames111 wrote:
Show quoteHide quote

> Hi
>
> my first post!!!
>
> i have an organisation table and i want to do something like this
>
> select count(orgname) as Count1 where orgname like 'A%'
> select count(orgname) as Count2 where orgname like 'B%'
>
> but i want it to list every organisation, so one on each row, then the
> 2nd column show the results of the first select query and the 3rd
> column to show the result of the 2nd select query so ill have something
> like this:
>
>
> Orgname1     count1     count2
> Orgname2     count1     count2
> Orgname3     count1     count2
> Orgname4     count1     count2
> Orgname5     count1     count2
>
> i know that count1 and count2 will always be the same on each row, but
> i have just simplified my query for the sake of this post.
>
> so basically for each organisation in the table, i wanna perform a few
> queries that relate to that particular organisation.
>
> any help would be great as im totally stuck
>
> andrew

The best way to post a problem like this is to include enough code to
reproduce it (CREATE TABLE and INSERTs) and then show the end result
you want. For example:

CREATE TABLE orgs (orgname VARCHAR(10) NOT NULL PRIMARY KEY);
INSERT INTO orgs VALUES ('AAA');
INSERT INTO orgs VALUES ('ABC');
INSERT INTO orgs VALUES ('BBB');

If I've understood you correctly, try this:

SELECT orgs.orgname, O.count1, O.count2
FROM orgs,
(SELECT
   COUNT(CASE WHEN orgname LIKE 'A%' THEN 1 END) AS count1,
   COUNT(CASE WHEN orgname LIKE 'B%' THEN 1 END) AS count2
   FROM orgs
   WHERE orgname LIKE 'A%'
    OR orgname LIKE 'B%') AS O;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

21 Apr 2006 11:42 AM

Dan Guzman

There are different methods depending on the 'count1' and 'count2' queries.
One method (untested):

SELECT
    OrganizationName,
    (SELECT COUNT(*)
        FROM Table1
        WHERE Table1.OrganizationID = Organization.OrganizationID)
        AS Count1,
    (SELECT COUNT(*)
        FROM Table2
        WHERE Table2.OrganizationID = Organization.OrganizationID)
        AS Count2
FROM Organization
ORDER BY OrganizationName

Another method (untested):

SELECT
    Organization.OrganizationName,
    SUM(CASE WHEN Region = 'East' THEN 1 ELSE 0 END) AS Count1,
    SUM(CASE WHEN Region = 'West' THEN 1 ELSE 0 END) AS Count2
FROM Organization
JOIN Table1 ON
    Table1.OrganizationID = Organization.OrganizationID
GROUP BY Organization.OrganizationName
ORDER BY Organization.OrganizationName

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quoteHide quote

"Ames111" <andrew.a***@4projects.com> wrote in message
news:1145617354.870638.194390@v46g2000cwv.googlegroups.com...
> Hi
>
> my first post!!!
>
> i have an organisation table and i want to do something like this
>
> select count(orgname) as Count1 where orgname like 'A%'
> select count(orgname) as Count2 where orgname like 'B%'
>
> but i want it to list every organisation, so one on each row, then the
> 2nd column show the results of the first select query and the 3rd
> column to show the result of the 2nd select query so ill have something
> like this:
>
>
> Orgname1     count1     count2
> Orgname2     count1     count2
> Orgname3     count1     count2
> Orgname4     count1     count2
> Orgname5     count1     count2
>
> i know that count1 and count2 will always be the same on each row, but
> i have just simplified my query for the sake of this post.
>
> so basically for each organisation in the table, i wanna perform a few
> queries that relate to that particular organisation.
>
> any help would be great as im totally stuck
>
> andrew
>

Thread options