is it possible?

mariommariom

Hi all,

I have the following question.

Is it possible to make a query such that the result will be displayed in the following format:

Expand|Select|Wrap|Line Numbers

 
country       totalInstitutions       noInstitutionsMet

Germany             15                            7

U.S.                   10                            0

France                 5                            4

Basically to show the total no. of Institutions and the no of Institutions that met the customer X, grouped by country.

having the following tables:
institution (id, name, country, etc.)
customers (idCustomer, idInstitution, nameCustomer)
meetings (idmeeting, idCustomer, idInstitution)

Thank you!

Atli

Hey.

Sure, that's possible.

You could use do a simple SELECT to get the Country and TotalInstitutions values, and add a sub-query that counts the Institutions based on whatever filter you need.

Like, for example, if you needed the total number of cars sold by each car dealership in a database, as well as the total number of cars in a specific color:

Expand|Select|Wrap|Line Numbers

 SELECT

    `Dealership`,

    COUNT(`Cars`) AS 'Total Cars',

    (    SELECT COUNT(*)

        FROM `CarTable` AS `inner`

        WHERE 

            `outer`.`Dealership` = `inner`.`Dealership`

        AND `inner`.`Color` = 'Red'

    ) AS 'Total Red Cars'

FROM `CarTable` AS `outer`

GROUP BY `Dealership`

You might get something like:

Expand|Select|Wrap|Line Numbers

 +------------+------------+----------------+

| Dealership | Total Cars | Total Red Cars |

+------------+------------+----------------+

| First      |         10 |              3 |

| Second     |         23 |             12 |

| Second     |         16 |              7 |

+------------+------------+----------------+

mariommariom

Thank you Atli!

I got some results but the row with the "Total Red Cars" gives the same numer, which is not correct. If I try to group by the sub-query, I will get an errow: "Subquery returns more than one row".

Here is the real query I am using. Thank you once more!

Expand|Select|Wrap|Line Numbers

  SELECT Address.country, count( Address.country ) AS CompanyTotal, (

SELECT COUNT( * )

    FROM Company

    INNER JOIN Position ON ( Company.orgId = Position.compId )

    INNER JOIN Address ON ( Company.addressId = Address.addressId )

    INNER JOIN Meeting ON ( Company.orgId = Meeting.company )

    INNER JOIN EventDefinition ON ( Meeting.event = EventDefinition.idEventDef )

    WHERE Position.customer = 'XXX'

    AND Meeting.customer = 'XXX'

    AND Position.nbrOfShare !=0

    AND EventDefinition.date > '2009-01-01 00:00:00'

    ) AS CompanyMet

FROM Company

INNER JOIN Position ON ( Company.orgId = Position.compId )

INNER JOIN Address ON ( Company.addressId = Address.addressId )

WHERE customer = 'XXX'

AND Position.nbrOfShare !=0

GROUP BY Address.country

ORDER BY ct DESC

is it possible?

re: is it possible?

re: is it possible?

Similar MySQL Database bytes

/bytes/development

/bytes/it

/bytes/about