CODEWITHSUNDEEP
phpmysqldatabase
WebDevKev
WebDevKev

Reputation: 285

How do I generate several reports from one MySQL query?

I need to report the number of records that match each of several criteria. For example, how many customers live in each state. I know I can accomplish this through a series of MySQL SELECT statements, but that seems cumbersome and produces a ton of (unnecessary?) MySQL calls.

Can you tell me a better method to use? Can I query the database with one SELECT statement and then use PHP to filter the results to variables?

Upvotes: 3

Views: 234

Answers (2)

user3146706
user3146706

Reputation: 69

create select query make number of aliens of table and make your related columns aliens which is you want to use. lest see sample example

SELECT a.id AS id FROM Table1 AS a WHERE ... UNION ALL SELECT b.name AS name FROM Table2 AS b WHERE ... ORDER BY (a or b) ...

Upvotes: 0

crush
crush

Reputation: 17013

I'd suggest creating a view for this task just to hide the complexity of the query. Also, in the event that your table schema changes, it is likely that you are still going to want to retrieve this same information from the database. You'd be able to change the view in one place, instead of having to change the queries in, possibly, multiple places to satisfy your schema changes.

I'll just show you the queries, though, since you'd need to know how to do that to create a view anyways.

Sticking with your example of customers living in each state, let's pretend you also want statistics on how many customers share the same last name.

I've setup a mock structure of what your database might be like at this SqlFiddle.

Customers with Same LastName

The following query might be used to get the number of customers with the same last name:

SELECT
  LastName AS "Value",
  COUNT(*) AS "Count"
FROM Customers
GROUP BY
  LastName;

Customers in Same State

Similarly, the customers in the same state might be retrieved with a query as follows:

SELECT
  S.Name AS "Value",
  COUNT(*) AS "Count"
FROM Customers AS C
  INNER JOIN CustomerAddresses AS CA ON C.Id = CA.CustomerId
  INNER JOIN Addresses AS A ON CA.AddressId = A.Id
  INNER JOIN States AS S ON A.State = S.Id
GROUP BY
  A.State;

Getting Your Desired Format

The format that you want is an aggregation of these two queries. You want both returned as a single result set. So, let's workout a schema for the returned table:

  • ResultType - This will hold a value that corresponds to the type of result. i.e. "State"
  • Value - This will hold the value of the aggregated column. i.e. "Florida"
  • Count - This will hold the total number of records that match the aggregated column.

So, now that we have a format, let's create a query that uses our two queries from above, and puts them into this format.

First, I add a new field to each of the above queries: ResultType

For example:

"LastName" AS "ResultType"

Now, I combine my queries into a single query using the UNION ALL statement:

SELECT * FROM (

  /* LastName query */
  SELECT
    "LastName" AS "ResultType",
    LastName AS "Value",
    COUNT(*) AS "Count"
  FROM Customers
  GROUP BY
    LastName

  UNION ALL

  /* States query */
  SELECT
    "State" AS "ResultType",
    S.Name AS "Value",
    COUNT(*) AS "Count"
  FROM Customers AS C
    INNER JOIN CustomerAddresses AS CA ON C.Id = CA.CustomerId
    INNER JOIN Addresses AS A ON CA.AddressId = A.Id
    INNER JOIN States AS S ON A.State = S.Id
  GROUP BY
    A.State
) AS A

In my SqlFiddle above, this produces an output like:

RESULTTYPE   VALUE    COUNT
=================================
LastName     Jetson   1
LastName     Johnson  2
LastName     Milton   1
State        Florida  2
State        Georgia  1
State        Utah     1

As you can see, this could get quite complex, so you might consider looking into placing this into a view. Then, you'd be able to query your view, as if it was the table above (ResultType, Value, and Count). That would also allow you to filter on it.

Upvotes: 2

Related Questions