Getting Distinct Value Counts Across Multiple Fields/Tables in One MySQL Query

Question

I have a table called visits which contains the following link_id, id, browser, country, referer

Now, this basically records visits of a certain link and inserts the browser, country and referer of whomever visted that link in a database

Now I need to show statistics for each link

I used the following query to get me all the browsers

SELECT browser, COUNT(browser) FROM visits GROUP BY browser

Which produced something like

Browser Count(Browser) Internet Explorer | 5 Chrome | 3

Now this worked as expected for browsers only but I'm looking for a way to count all occurrences of referers, browsers and countries in one single query.

Is there a way to do this?

Please edit your question with sample data and desired results. It is not clear what results you want. — Gordon Linoff, Jul 15 '14 at 01:19
Duplicate: http://stackoverflow.com/questions/12789396/how-to-get-multiple-counts-with-one-sql-query — Kodlee Yin, Jul 15 '14 at 01:19
@KodleeYin . . . It is quite unclear whether this question is a duplicate of that one. There is not enough information about what the OP wants. — Gordon Linoff, Jul 15 '14 at 01:38
You really need to put the result you want into your question. Without it we can only guess. — Bob, Jul 15 '14 at 01:55

score 0 · Answer 1 · answered Feb 21 '20 at 20:25

To count multiple, different occurence counts of values in the DB can very easily be done in just one query.

Keep in mind, the column header in SELECT COUNT(tablename) returns only one column, with only one numeric value. For every distinct value (from the GROUP BY clause), you have two columns: Value, Count. To count for different fields, you'll need three: Field, Value, Count, and if you want to count different fields in different tables, you'll need four: Table, Field, Value, Count.

Observe how I am using UNION below for two different tables:

SELECT
        "Table1" AS TableName,
        "Field1" AS Field,
        Field1 AS Value, 
        COUNT(Field1) AS COUNT
    FROM Table1
    GROUP BY Value
UNION
SELECT
        "Table2" as TableName,
        "Field2" as Field,
        Field2 as Value,
        COUNT(Field2) AS COUNT
    FROM Table2
    GROUP BY Value

You'll notice I need to use aliases: "Table2" as TableName, this is because the UNION'd columns ought to have matching column headers.

So you can visualize what this returns, take a look:

+-------------------+----------------+----------+--------+
| TableName         | Field          | Value    | COUNT  |
+-------------------+----------------+----------+--------+
| ItemFee           | PaymentType    |          |    228 |
| ItemFee           | PaymentType    | All      |      1 |
| ItemFee           | PaymentType    | PaidOnly |      1 |
| Person            | Presenter      |          | 692258 |
| Person            | Presenter      | N        |    590 |
| Person            | Presenter      | Y        |   8103 |
+-------------------+----------------+----------+--------+

Getting Distinct Value Counts Across Multiple Fields/Tables in One MySQL Query

1 Answers1