Yes, you can group by multiple columns. For example,

    SELECT * FROM table
    GROUP BY col1, col2

The results will first be grouped by col1, then by col2. In MySQL, column preference goes from left to right.


Yes, but what does grouping by more two columns mean? Well, it&#39;s the same as grouping by each unique pair per row. The order you list the columns changes the way the rows are sorted.

In your example, you would write 

`GROUP BY fV.tier_id, f.form_template_id`


Meanwhile, the code

`GROUP BY f.form_template_id, fV.tier_id`

would give similar results, but sorted differently.




    group by fV.tier_id, f.form_template_id

To use a simple example, I had a counter that needed to summarise unique IP addresses per visited page on a site. Which is basically grouping by pagename and then by IP. I solved it with a combination of DISTINCT and GROUP BY.

    SELECT pagename, COUNT(DISTINCT ipaddress) AS visit_count FROM log_visitors GROUP BY pagename ORDER BY visit_count DESC;

If you prefer (I need to apply this) group by two columns at same time, I just saw this point:

    SELECT CONCAT (col1, &#39;_&#39;, col2) AS Group1 ... GROUP BY Group1

    GROUP BY CONCAT(col1, &#39;_&#39;, col2)

Is there a way to set up a global variable inside of a module? When I tried to do it the most obvious way as appears below, the Python interpreter said the variable `__DBNAME__` did not exist.

    ...
    __DBNAME__ = None
    
    def initDB(name):
        if not __DBNAME__:
            __DBNAME__ = name
        else:
            raise RuntimeError(&quot;Database name has already been set.&quot;)
    ...

And after importing the module in a different file

    ...
    import mymodule
    mymodule.initDB(&#39;mydb.sqlite&#39;)
    ...

And the traceback was:

&gt; ...
&gt; UnboundLocalError: local variable &#39;__DBNAME__&#39; referenced before assignment
&gt; ...


Any ideas? I&#39;m trying to set up a singleton by using a module, as per [this fellow&#39;s][1] recommendation.


  [1]: https://stackoverflow.com/questions/31875/is-there-a-simple-elegant-way-to-define-singletons-in-python/31887#31887

How to create module-wide variables in Python?

Is there a way using SQL to list all foreign keys for a given table? I know the table name / schema and I can plug that in.

How to list table foreign keys

Is it possible to `GROUP BY` more than one column in a MySQL `SELECT` query? For example:  

    GROUP BY fV.tier_id AND &#39;f.form_template_id&#39;

Is it possible to GROUP BY multiple columns using MySQL?

Is it possible to <code>GROUP BY</code> more than one column in a MySQL <code>SELECT</code> query? For example:
<pre><code class="hljs language-vbnet">GROUP BY fV.tier_id AND 'f.form_template_id'
</code></pre>

Is there going to be much benefit in indexing a boolean field in a database table?

Given a common situation, like &quot;soft-delete&quot; records which are flagged as inactive, and hence most queries include `WHERE deleted = 0`, would it help to have that field indexed on its own, or should it be combined with the other commonly-searched fields in a different index?

Indexing boolean fields

I want to input telephone number in a form, including country code, extension



    create table if not exists employee(	`	
          country_code_tel   int(11),
          tel_number         int(10),
          extension          int(10),
          mobile             bigint(20)
    );

If tel_number is larger than 15 bit, which datatype can I use, I&#39;d better use `Bigint(20)`?


    create table address(
          address           varchar(255),  
          city              varchar(255),
          country           varchar(255),
          post_code         int(11)
    );

For example, if I have a country code for Canada I can use +2 or 002. Which is better for processing?  

Thanks for your advice. 


mysql datatype for telephone number and address

I&#39;m using a system where the dates are stored as strings in the format `dd/mm/yyyy`. Is it possible to convert this to `yyyy-mm-dd` in a SELECT query (so that I can use `DATE_FORMAT` on it)? Does MySQL have a date parsing function?

Currently the only method I can think of is to concatenate a bunch of substrings, but hopefully there&#39;s a simpler solution.

(Unfortunately I can&#39;t convert the field to a true date field since it&#39;s a meta-table: the same column contains values for different fields that are just strings.)

Converting a date in MySQL from string field

In MySQL, can I select columns only where something exists? 

For example, I have the following query:

    select phone, phone2
    from jewishyellow.users
    where phone like &#39;813%&#39;
    and phone2

I&#39;m trying to select only the rows where phone starts with 813 and phone2 has something in it.

MySQL select where column is not empty

    SELECT * FROM table ORDER BY string_length(column);

Is there a MySQL function to do this (of course instead of `string_length`)?



MySQL - How to select data by string length

What&#39;s the difference between the SQL datatype `NUMERIC` and `DECIMAL` ?
If databases treat these differently, I&#39;d like to know how for at least:

 - SQL Server
 - Oracle
 - Db/2
 - MySQL
 - PostgreSQL

Furthermore, are there any differences in how database drivers interpret these types?


Difference between DECIMAL and NUMERIC

I have two tables where column `[date]` is type of `DATETIME2(0)`.

I have to compare two records only by theirs Date parts (day+month+year), discarding Time parts (hours+minutes+seconds).

How can I do that?

Compare DATETIME and DATE ignoring time portion

In SQL server if you have  `nullParam=NULL` in a where clause, it always evaluates to false. This is counterintuitive and has caused me many errors. I do understand the `IS NULL` and `IS NOT NULL` keywords are the correct way to do it. But why does SQL server behave this way?

Why does NULL = NULL evaluate to false in SQL server

I have an sql column that is a string of 100 &#39;Y&#39; or &#39;N&#39; characters. For example: 

&gt; YYNYNYYNNNYYNY...

What is the easiest way to get the count of all &#39;Y&#39; symbols in each row.

How to count instances of character in SQL Column

What is the correction needed for **example 2** inorder to group by multiple columns

**Example 1**

    var query = from cm in cust
                group cm by new { cm.Customer, cm.OrderDate } into cms
                select
                new 
                { Key1 = cms.Key.Customer,Key2=cms.Key.OrderDate,Count=cms.Count() };

**Example 2 (incorrect)**

       var qry = 
       cust.GroupBy(p =&gt; p.Customer, q =&gt; q.OrderDate, (k1, k2, group) =&gt;
       new { Key1 = k1, Key2 = k2, Count = group.Count() });
            


LINQ Group By Multiple fields -Syntax help

how do I write this query in linq (vb.net)?

    
     select B.Name
     from Company B
     group by B.Name
     having COUNT(1) &gt; 1

Linq with group by having count

The following query:

	SELECT
	year, id, rate
	FROM h
	WHERE year BETWEEN 2000 AND 2009
	AND id IN (SELECT rid FROM table2)
	GROUP BY id, year
	ORDER BY id, rate DESC

yields:

	year	id	rate
	2006	p01	8
	2003	p01	7.4
	2008	p01	6.8
	2001	p01	5.9
	2007	p01	5.3
	2009	p01	4.4
	2002	p01	3.9
	2004	p01	3.5
	2005	p01	2.1
	2000	p01	0.8
	2001	p02	12.5
	2004	p02	12.4
	2002	p02	12.2
	2003	p02	10.3
	2000	p02	8.7
	2006	p02	4.6
	2007	p02	3.3

What I&#39;d like is only the top 5 results for each id:

	2006	p01	8
	2003	p01	7.4
	2008	p01	6.8
	2001	p01	5.9
	2007	p01	5.3
	2001	p02	12.5
	2004	p02	12.4
	2002	p02	12.2
	2003	p02	10.3
	2000	p02	8.7

Is there a way to do this using some kind of LIMIT like modifier that works within the GROUP BY?


Using LIMIT within GROUP BY to get N results per group?

I have a query which uses the GROUP_CONCAT of mysql on an integer field.  
I am using PHPMYADMIN to develop this query. My problem that instead of showing 1,2 which is the result of the concatenated field, I get [BLOB - 3B].  

Query is

    SELECT rec_id,GROUP_CONCAT(user_id)
    FROM t1
    GROUP BY rec_id
(both fields are unsigned int, both are not unique)

What should I add to see the actual results?

using group_concat in PHPMYADMIN will show the result as [BLOB - 3B]

I understand the point of `GROUP BY x`.

But how does `GROUP BY x, y` work, and what does it mean?

Content Type	Original Author	Original Content on Stackoverflow
Question	Rhys Thought	View Question on Stackoverflow
Solution 1 - Mysql	Joe Phillips	View Answer on Stackoverflow
Solution 2 - Mysql	php	View Answer on Stackoverflow
Solution 3 - Mysql	brandonCabi	View Answer on Stackoverflow
Solution 4 - Mysql	Trevor	View Answer on Stackoverflow
Solution 5 - Mysql	Daniklad	View Answer on Stackoverflow
Solution 6 - Mysql	Lucas Andrade	View Answer on Stackoverflow
Solution 7 - Mysql	lada	View Answer on Stackoverflow

Is it possible to GROUP BY multiple columns using MySQL?

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Solution 3 - Mysql

Solution 4 - Mysql

Solution 5 - Mysql

Solution 6 - Mysql

Solution 7 - Mysql

How to list table foreign keys

How to create module-wide variables in Python?

Attributions