One way would be to use a nested query:

    SELECT count(*)
    FROM (
       SELECT COUNT(Genre) AS count
       FROM movies
       GROUP BY ID
       HAVING (count = 4)
    ) AS x

The inner query gets all the movies that have exactly 4 genres, then outer query counts how many rows the inner query returned.

    SELECT COUNT(*) 
    FROM   (SELECT COUNT(*) 
            FROM   movies 
            GROUP  BY id 
            HAVING COUNT(genre) = 4) t

Maybe  

    SELECT count(*) FROM (
        SELECT COUNT(*) FROM Movies GROUP BY ID HAVING count(Genre) = 4
    ) AS the_count_total

although that would not be the sum of all the movies, just how many have 4 genre&#39;s.

So maybe you want  
    
    SELECT sum(
        SELECT COUNT(*) FROM Movies GROUP BY ID having Count(Genre) = 4
    ) as the_sum_total

What about:

    SELECT COUNT(*) FROM (SELECT ID FROM Movies GROUP BY ID HAVING COUNT(Genre)=4) a

How to simply flatten array in jQuery? I have: 

    [1, 2, [3, 4], [5, 6], 7]

And I want: 

    [1, 2, 3, 4, 5, 6, 7]

How to flatten array in jQuery?

I want to get two ints, one divided by the other to get a decimal or percentage.
How can I get a percentage or decimal of these two ints?
(I&#39;m not sure if it is right.. I&#39;m probably way off...)
for example:

    int correct = 25;
    int questionNum = 100;
    float percent = correct/questionNum *100;

This is how I thought I could do it, but it didn&#39;t work... I want to make the decimal (if there is one) into a percent out of 100 for example in this case it is %25. any ideas anyone?

Here is the correct code (thanks to Salvatore Previti!):
 
    float correct = 25;
    float questionNum = 100;
    float percent = (correct * 100.0f) / questionNum;
    

(btw, I am making a project using this for a quiz checking program that is why I need the percentage or decimal)

percentage of two int?

I have this table:

    Movies (ID, Genre)

A movie can have multiple genres, so an ID is not specific to a genre, it is a many to many relationship. I want a query to find the total number of movies which have at exactly 4 genres. The current query I have is 

      SELECT COUNT(*) 
        FROM Movies 
    GROUP BY ID 
      HAVING COUNT(Genre) = 4

However, this returns me a list of 4&#39;s instead of the total sum. How do I get the sum total sum instead of a list of `count(*)`?

mysql count group by having

I have this table:
<pre><code class="hljs language-scss">Movies (ID, Genre)
</code></pre>
A movie can have multiple genres, so an ID is not specific to a genre, it is a many to many relationship. I want a query to find the total number of movies which have at exactly 4 genres. The current query I have is
<pre><code class="hljs language-sql"> SELECT COUNT(*) 
 FROM Movies 
GROUP BY ID 
 HAVING COUNT(Genre) = 4
</code></pre>
However, this returns me a list of 4's instead of the total sum. How do I get the sum total sum instead of a list of <code>count(*)</code>?

This has been asked on this site before but I couldn&#39;t find a sufficient answer. If I&#39;m doing a query like:

    Select Seller from Table where Location = &#39;San Jose&#39;

How can I make it return only Sellers with Location &#39;San Jose&#39; instead of &#39;san jose&#39; or something else?

MySQL case sensitive query

Could you help me solve this problem ?

When I try to click &quot;query database&quot; under database menu in Mysql workbench. it gives me an error:

&gt; Cannot Connect to Database Server
&gt;
&gt; Your connection attempt failed for user &#39;root&#39; from your host to server at 
    127.0.0.1:3306:Can&#39;t connect to mysql server on &#39;127.0.0.1&#39;(10061)
&gt;
&gt; Please:
&gt;
&gt; 1. Check that mysql is running on server 127.0.0.1
&gt; 2. Check that mysql is running on port 3306 (note: 3306 is the default, but this can be changed)
&gt; 3. Check the root has rights to connect to 127.0.0.1 from your address (mysql rights define what clients can connect to the server and from which machines) 
&gt; 4. Make sure you are both providing a password if needed and using the correct password for 127.0.0.1 connecting from the host address you&#39;re connecting from


Cannot connect to Database server (mysql workbench)

Here&#39;s how I do it:

 1. Table names are lower case, uses underscores to separate words, and are singular (e.g. `foo`, `foo_bar`, etc.
 2. I generally (not always) have a auto increment PK. I use the following convention: `tablename_id` (e.g. `foo_id`, `foo_bar_id`, etc.).
 3. When a table contains a column that is a foreign key, I just copy the column name of that key from whatever table it came from. For example, say table `foo_bar` has the FK `foo_id` (where `foo_id` is the PK of `foo`).
 4. When defining FKs to enforce referential integrity, I use the following: `tablename_fk_columnname` (e.g. furthering example 3, it would be `foo_bar_foo_id`). Since this is a table name/column name combination, it is guaranteed to be unique within the database.
 5. I order the columns like this: PKs, FKs, then the rest of columns alphabetically

Is there a better, more standard way to do this?



Is there a naming convention for MySQL?

I installed mySQL on my Mac. Beside starting the SQL server with mySQL.prefPane tool installed in System Preferences, I want to know the instructions to start from command-line.
I do as follows:

After

&gt; su root

I start the mySQL server by command-line, but it produces an error as below:

&gt; **sh-3.2#** /usr/local/mysql/bin/mysqld
&gt; 
&gt; **111028 16:57:43** [Warning] Setting lower_case_table_names=2 because
&gt; file system for /usr/local/mysql-5.5.17-osx10.6-x86_64/data/ is case
&gt; insensitive
&gt; 
&gt; **111028 16:57:43** [ERROR] Fatal error: Please read &quot;Security&quot; section of
&gt; the manual to find out how to run mysqld as root!
&gt; 
&gt; **111028 16:57:43** [ERROR] Aborting
&gt; 
&gt; **111028 16:57:43** [Note] /usr/local/mysql/bin/mysqld: Shutdown complete




How to start MySQL server from command line on Mac OS Lion?

What is the safest way to run queries on MySQL? I am aware of the dangers involved with MySQL and SQL injection.

However, I do not know how I should run my queries to prevent injection on the variables to which other users (webclients) can manipulate. I used to write my own escape function, but apparently this is &quot;not-done&quot;.

What should I use and how should I use it to query and do inserts safely on a MySQL database through python without risking MySQL injection?

Python: best practice and securest way to connect to MySQL and execute queries

Is it possible in Microsoft SQL Server generate random int value from Min to Max (3-9 example, 15-99 e.t.c)

I know, I can generate from 0 to Max, but how to increase Min border?

This query generate random value from 1 to 6. Need to change it from 3 to 6.

    SELECT table_name, 1.0 + floor(6 * RAND(convert(varbinary, newid()))) magic_number 
    FROM information_schema.tables

**Added 5 sec later**:

    SELECT table_name, 3.0 + floor(4 * RAND(convert(varbinary, newid()))) magic_number 
    FROM information_schema.tables

Generate random int value from 3 to 6

UPDATE 3: According to [this announcement][1], this has been addressed by the EF team in EF6 alpha 2.

UPDATE 2: I&#39;ve created a suggestion to fix this problem. To vote for it, [go here][2].

Consider a SQL database with one very simple table.

    CREATE TABLE Main (Id INT PRIMARY KEY)

I populate the table with 10,000 records.

    WITH Numbers AS
    (
      SELECT 1 AS Id
      UNION ALL
      SELECT Id + 1 AS Id FROM Numbers WHERE Id &lt;= 10000
    )
    INSERT Main (Id)
    SELECT Id FROM Numbers
    OPTION (MAXRECURSION 0)

I build an EF model for the table and run the following query in LINQPad (I am using &quot;C# Statements&quot; mode so LINQPad doesn&#39;t create a dump automatically).

    var rows = 
      Main
      .ToArray();

Execution time is ~0.07 seconds. Now I add the Contains operator and re-run the query.

    var ids = Main.Select(a =&gt; a.Id).ToArray();
    var rows = 
      Main
      .Where (a =&gt; ids.Contains(a.Id))
      .ToArray();

Execution time for this case is **20.14 seconds** (288 times slower)!

At first I suspected that the T-SQL emitted for the query was taking longer to execute, so I tried cutting and pasting it from LINQPad&#39;s SQL pane into SQL Server Management Studio.

    SET NOCOUNT ON
    SET STATISTICS TIME ON
    SELECT 
    [Extent1].[Id] AS [Id]
    FROM [dbo].[Primary] AS [Extent1]
    WHERE [Extent1].[Id] IN (1,2,3,4,5,6,7,8,...

And the result was

    SQL Server Execution Times:
      CPU time = 0 ms,  elapsed time = 88 ms.

Next I suspected LINQPad was causing the problem, but performance is the same whether I run it in LINQPad or in a console application.

So, it appears that the problem is somewhere within Entity Framework.

Am I doing something wrong here? This is a time-critical part of my code, so is there something I can do to speed up performance?

I am using Entity Framework 4.1 and Sql Server 2008 R2.

UPDATE 1:

In the discussion below there were some questions about whether the delay occurred while EF was building the initial query or while it was parsing the data it received back. To test this I ran the following code,

    var ids = Main.Select(a =&gt; a.Id).ToArray();
    var rows = 
      (ObjectQuery&lt;MainRow&gt;)
      Main
      .Where (a =&gt; ids.Contains(a.Id));
    var sql = rows.ToTraceString();

which forces EF to generate the query without executing it against the database. The result was that this code required ~20 secords to run, so it appears that almost all of the time is taken in building the initial query.

CompiledQuery to the rescue then? Not so fast ... CompiledQuery requires the parameters passed into the query to be fundamental types (int, string, float, and so on). It won&#39;t accept arrays or IEnumerable&lt;int&gt;, so I can&#39;t use it for a list of Ids.


  [1]: http://blogs.msdn.com/b/adonet/archive/2012/12/10/ef6-alpha-2-available-on-nuget.aspx
  [2]: http://data.uservoice.com/forums/72025-ado-net-entity-framework-ef-feature-suggestions/suggestions/2598644-improve-the-performance-of-the-contains-operator

Why does the Contains() operator degrade Entity Framework&#39;s performance so dramatically?

I am trying to create a table with an auto-incrementing `primary key` in [Sqlite3][1].  I am not sure if this is really possible, but I am hoping to only have to designate the other fields.

For example:

    CREATE TABLE people (id integer primary key auto increment, first_name varchar(20), last_name varchar(20));

Then, when I add a value, I was hoping to only have to do:

    INSERT INTO people
    VALUES (&quot;John&quot;, &quot;Smith&quot;);

Is this even possible?

I am running `sqlite3` under `cygwin` in Windows 7.


  [1]: https://www.sqlite.org/index.html

Is there an auto increment in sqlite?

I&#39;m trying to get a the key-value back after an INSERT-statement. 
Example: 
I&#39;ve got a table with the attributes name and id. id is a generated value.

        INSERT INTO table (name) VALUES(&#39;bob&#39;);
Now I want to get the id back in the same step. How is this done?

We&#39;re using Microsoft SQL Server 2008.

SQL Server - Return value after INSERT

[I posted a related question](https://stackoverflow.com/q/7922211/902825), but this is another part of my puzzle.

I would like to get the OLD value of a column from a row that was UPDATEd - WITHOUT using triggers (nor stored procedures, nor any other extra, non -SQL/-query entities).

I have a query like this:

       UPDATE my_table
          SET processing_by = our_id_info  -- unique to this worker
        WHERE trans_nbr IN (
                            SELECT trans_nbr
                              FROM my_table
                             GROUP BY trans_nbr
                            HAVING COUNT(trans_nbr) &gt; 1
                             LIMIT our_limit_to_have_single_process_grab
                           )
    RETURNING row_id;

If I could do `FOR UPDATE ON my_table` at the end of the subquery, that&#39;d be divine (and fix my other question/problem). But that won&#39;t work: can&#39;t combine this with `GROUP BY` (which is necessary for figuring out the count). Then I could just take those trans_nbr&#39;s and do a query first to get the (soon-to-be-) former `processing_by` values.

I&#39;ve tried doing like:

       UPDATE my_table
          SET processing_by = our_id_info -- unique to this worker
         FROM my_table old_my_table
         JOIN (
                 SELECT trans_nbr
                   FROM my_table
               GROUP BY trans_nbr
                 HAVING COUNT(trans_nbr) &gt; 1
                  LIMIT our_limit_to_have_single_process_grab
              ) sub_my_table
           ON old_my_table.trans_nbr = sub_my_table.trans_nbr
        WHERE     my_table.trans_nbr = sub_my_table.trans_nbr
          AND my_table.processing_by = old_my_table.processing_by
    RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by

But that can&#39;t work; `old_my_table` is not visible outside the join; the `RETURNING` clause is blind to it.

I&#39;ve long since lost count of all the attempts I&#39;ve made; I have been researching this for literally hours.

If I could just find a bullet-proof way to lock the rows in my subquery - and ONLY those rows, and WHEN the subquery happens - all the concurrency issues I&#39;m trying to avoid would disappear ...

---

**UPDATE:** I had a typo in the non-generic code of the above. I retried after Erwin Brandstetter suggested it should work. Since it took me so long to find this sort of solution, perhaps my embarrassment is worth it? At least this is on SO for posterity now...  :&gt;

What I now have (that works) is like this:

       UPDATE my_table
          SET processing_by = our_id_info -- unique to this worker
         FROM my_table AS old_my_table
        WHERE trans_nbr IN (
                              SELECT trans_nbr
                                FROM my_table
                            GROUP BY trans_nbr
                              HAVING COUNT(*) &gt; 1
                               LIMIT our_limit_to_have_single_process_grab
                           )
          AND my_table.row_id = old_my_table.row_id
    RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by AS old_processing_by

The `COUNT(*)` is per a suggestion from **Flimzy** in a comment on my other (linked above) question.

[Please see my other question](https://stackoverflow.com/q/7922211/902825) for correctly implementing concurrency and even a non-blocking version; THIS query merely shows how to get the old and new values from an update, ignore the bad/wrong concurrency bits.

Return pre-UPDATE column values using SQL only

I am performing some tests on a `HSQLDB` server with a table containing 500 000 entries. The table has no indices. There are 5000 distinct business keys. I need a list of them.

Naturally I started with a `DISTINCT` query:

    SELECT DISTINCT business_key
    FROM memory
    WHERE concept &lt;&gt; &#39;case&#39;   OR 
          attrib  &lt;&gt; &#39;status&#39; OR 
          value   &lt;&gt; &#39;closed&#39;;

It takes around 90 seconds!!!

Then I tried using `GROUP BY`:

    SELECT business_key
    FROM memory
    WHERE concept &lt;&gt; &#39;case&#39;   OR 
          attrib  &lt;&gt; &#39;status&#39; OR
          value   &lt;&gt; &#39;closed&#39;;
    GROUP BY business_key

And it takes 1 second!!!

Trying to figure out the difference I ran `EXLAIN PLAN FOR` but it seems to give the same information for both queries.

`EXLAIN PLAN FOR DISTINCT ...`

    isAggregated=[false]
    columns=[
      COLUMN: PUBLIC.MEMORY.BUSINESS_KEY
    ]
    [range variable 1
      join type=INNER
      table=MEMORY
      alias=M
      access=FULL SCAN
      condition = [    index=SYS_IDX_SYS_PK_10057_10058
        other condition=[
        OR arg_left=[
         OR arg_left=[
          NOT_EQUAL arg_left=[
           COLUMN: PUBLIC.MEMORY.CONCEPT] arg_right=[
           VALUE = case, TYPE = CHARACTER]] arg_right=[
          NOT_EQUAL arg_left=[
           COLUMN: PUBLIC.MEMORY.ATTRIB] arg_right=[
           VALUE = status, TYPE = CHARACTER]]] arg_right=[
         NOT_EQUAL arg_left=[
          COLUMN: PUBLIC.MEMORY.VALUE] arg_right=[
          VALUE = closed, TYPE = CHARACTER]]]
      ]
    ]]
    PARAMETERS=[]
    SUBQUERIES[]
    Object References
    PUBLIC.MEMORY
    PUBLIC.MEMORY.CONCEPT
    PUBLIC.MEMORY.ATTRIB
    PUBLIC.MEMORY.VALUE
    PUBLIC.MEMORY.BUSINESS_KEY
    Read Locks
    PUBLIC.MEMORY
    WriteLocks


`EXLAIN PLAN FOR SELECT ... GROUP BY ...`

    isDistinctSelect=[false]
    isGrouped=[true]
    isAggregated=[false]
    columns=[
      COLUMN: PUBLIC.MEMORY.BUSINESS_KEY
    ]
    [range variable 1
      join type=INNER
      table=MEMORY
      alias=M
      access=FULL SCAN
      condition = [    index=SYS_IDX_SYS_PK_10057_10058
        other condition=[
        OR arg_left=[
         OR arg_left=[
          NOT_EQUAL arg_left=[
           COLUMN: PUBLIC.MEMORY.CONCEPT] arg_right=[
           VALUE = case, TYPE = CHARACTER]] arg_right=[
          NOT_EQUAL arg_left=[
           COLUMN: PUBLIC.MEMORY.ATTRIB] arg_right=[
           VALUE = status, TYPE = CHARACTER]]] arg_right=[
         NOT_EQUAL arg_left=[
          COLUMN: PUBLIC.MEMORY.VALUE] arg_right=[
          VALUE = closed, TYPE = CHARACTER]]]
      ]
    ]]
    groupColumns=[
    COLUMN: PUBLIC.MEMORY.BUSINESS_KEY]
    PARAMETERS=[]
    SUBQUERIES[]
    Object References
    PUBLIC.MEMORY
    PUBLIC.MEMORY.CONCEPT
    PUBLIC.MEMORY.ATTRIB
    PUBLIC.MEMORY.VALUE
    PUBLIC.MEMORY.BUSINESS_KEY
    Read Locks
    PUBLIC.MEMORY
    WriteLocks

---
## EDIT
I did additional tests. With 500 000 records in `HSQLDB` with all distinct business keys, the performance of `DISTINCT` is now better - 3 seconds, vs `GROUP BY` which took around 9 seconds.

In `MySQL` both queries preform the same:

MySQL: 500 000 rows - 5 000 distinct business keys:
Both queries: 0.5 second
MySQL: 500 000 rows - all distinct business keys:
`SELECT DISTINCT ...` - 11 seconds
`SELECT ... GROUP BY business_key` - 13 seconds

So the problem is only related to `HSQLDB`.

I will be very grateful if someone can explain why there is such a drastic difference.


Huge performance difference when using GROUP BY vs DISTINCT

I have following query in PostgreSQL:

    SELECT 
    	COUNT(a.log_id) AS overall_count
    FROM 
    	&quot;Log&quot; as a, 
    	&quot;License&quot; as b 
    WHERE 
    	a.license_id=7 
    AND 
    	a.license_id=b.license_id 
    AND
    	b.limit_call &gt; overall_count
    GROUP BY 
    	a.license_id;

Why do I get this error:

&gt; ERROR:  column &quot;overall_count&quot; does not exist

My table structure:

    License(license_id, license_name, limit_call, create_date, expire_date)
    Log(log_id, license_id, log, call_date)

I want to check if a license has reached the limit for calls in a specific month.

PostgreSQL Where count condition

	(SELECT COUNT(motorbike.`owner_id`) as count,owner.`name`,transport.`type` FROM transport,owner,motorbike WHERE transport.type=&#39;motobike&#39;
	AND owner.`owner_id`=motorbike.`owner_id`
	AND transport.`type_id`=motorbike.`motorbike_id` GROUP BY motorbike.owner_id)
	UNION ALL
	(SELECT COUNT(car.`owner_id`) as count,owner.`name`,transport.`type` FROM transport,owner,car WHERE transport.type=&#39;car&#39;
	AND owner.`owner_id`=car.`owner_id`
	AND transport.`type_id`=car.`car_id` GROUP BY car.`owner_id`)


The query above returns a result like this below,

    count          name
    1              Linda
    2              Mary
    1              Steve
    1              Linda

This query is to count how many transport that owned by an owner. Linda have one car and one motorcycle,so the result should:

    count          name
    2              Linda
    2              Mary
    1              Steve

I have tried this query,but return error:


	(SELECT COUNT(motorbike.`owner_id`),owner.`name`,transport.`type` FROM transport,owner,motorbike WHERE transport.type=&#39;motobike&#39;
	AND owner.`owner_id`=motorbike.`owner_id`
	AND transport.`type_id`=motorbike.`motorbike_id`)
	UNION ALL
	(SELECT COUNT(car.`owner_id`),owner.`name`,transport.`type` FROM transport,owner,car WHERE transport.type=&#39;car&#39;
	AND owner.`owner_id`=car.`owner_id`
	AND transport.`type_id`=car.`car_id`)  GROUP BY motorbike.owner_id

Can anyone help me please?


Group by with union mysql select query

I have a query that combines a join and a group, but I have a problem. The query is like:

     var result = from p in Products                         
     join bp in BaseProducts on p.BaseProductId equals bp.Id                    
     group p by p.SomeId into pg                         
     select new ProductPriceMinMax { 
           SomeId = pg.FirstOrDefault().SomeId, 
           CountryCode = pg.FirstOrDefault().CountryCode, 
           MinPrice = pg.Min(m =&gt; m.Price), 
           MaxPrice = pg.Max(m =&gt; m.Price),
           BaseProductName = bp.Name  &lt;------ can&#39;t use bp. 
     };

As you see, it joins the `Products` table with the `BaseProducts` table, and groups on an id of the `Product` table. But in the resulting `ProductPriceMinMax`, I also need a property of the `BaseProducts` table: `bp.Name`, but it doesn&#39;t know `bp`. 

Any idea what I&#39;m doing wrong?

LINQ: combining join and group by

My table looks like this (and I&#39;m using MySQL):

    m_id | v_id | timestamp
    ------------------------
    6    |   1  | 1333635317
    34   |   1  | 1333635323
    34   |   1  | 1333635336
    6    |   1  | 1333635343
    6    |   1  | 1333635349

My target is to take each m_id one time, and order by the highest timestamp.

The result should be:

    m_id | v_id | timestamp
    ------------------------
    6    |   1  | 1333635349
    34   |   1  | 1333635336

And i wrote this query:

    SELECT * FROM table GROUP BY m_id ORDER BY timestamp DESC

But, the results are:

    m_id | v_id | timestamp
    ------------------------
    34   |   1  | 1333635323
    6    |   1  | 1333635317

I think it causes because it first does GROUP_BY and then ORDER the results.

Any ideas? Thank you.

Using ORDER BY and GROUP BY together

In SQL Server 2008 I have a table **CUSTOMERS** that has two columns as:

**ID,
BALANCE**

How can I write the query that selects the ID of the customer who has maximum balance, &quot;*in the most effective way*&quot;?

Option 1: `ORDER BY BALANCE and SELECT TOP(1)` --&gt; costs too much.

Option 2: Firstly `Get MAX amount`, then make another query that uses the amount in `where clause` --&gt; costs too much and not seem reliable.

Selecting a Record With MAX Value

I have a data frame with several columns; some numeric and some character. **How to compute the&#160;sum of a specific column?** I’ve googled for this and I see numerous functions (`sum`, `cumsum`, `rowsum`, `rowSums`, `colSums`, `aggregate`, `apply`) but I can’t make sense of it all.

For example suppose I have a data frame `people` with the following columns


    people &lt;- read(
      text = 
        &quot;Name Height Weight
        Mary 65     110
        John 70     200
        Jane 64     115&quot;, 
      header = TRUE
    )
    …

How do I get the sum of all the weights?

How to sum data.frame column values?

This has probably been asked before, but I&#39;m unable to make my way through the myriad of search results.

Given a non-normalized MySQL table, what is the most optimized query to count the number of times each distinct value of column x was used?

e.g. Given a table containing

    mike
    mary
    mike

Return results like:

    mike 2
    mary 1

From the MySQL documentation, it would seem that `count` is an aggregate function that can be used with `GROUP BY`, but it&#39;s not doing what I want (it&#39;s returning the total number of rows in the `GROUP BY`, not the number of appearances for each row. i.e. this does not work `SELECT count(email) as c FROM orders GROUP BY email`

Count number of times value appears in particular column in MySQL

I am trying to use the `LISTAGG` function in Oracle. I would like to get only the distinct values for that column. Is there a way in which I can get only the distinct values without creating a function or a procedure?

&lt;pre&gt;
  col1  col2 Created_by
   1     2     Smith 
   1     2     John 
   1     3     Ajay 
   1     4     Ram 
   1     5     Jack 
&lt;/pre&gt;

I need to select col1 and the `LISTAGG` of col2 (column 3 is not considered). When I do that, I get something like this as the result of `LISTAGG`: *[2,2,3,4,5]* 

I need to remove the duplicate &#39;2&#39; here; I need only the distinct values of col2 against col1.

LISTAGG in Oracle to return distinct values

I&#39;m finding a way to aggregate strings from different rows into a single row. I&#39;m looking to do this in many different places, so having a function to facilitate this would be nice. I&#39;ve tried solutions using `COALESCE` and `FOR XML`, but they just don&#39;t cut it for me.

String aggregation would do something like this:

    id | Name                    Result: id | Names
    -- - ----                            -- - -----
    1  | Matt                            1  | Matt, Rocks
    1  | Rocks                           2  | Stylus
    2  | Stylus

I&#39;ve taken a look at [CLR-defined aggregate functions][1] as a replacement for `COALESCE` and `FOR XML`, but apparently **SQL Azure** *does not* support CLR-defined stuff, which is a pain for me because I know being able to use it would solve a whole lot of problems for me.

Is there any possible workaround, or similarly optimal method (which might not be as optimal as CLR, but *hey* I&#39;ll take what I can get) that I can use to aggregate my stuff?

[1]: http://picnicerror.net/development/sql-server/concatenate-multiple-row-values-sql-server-with-clr-2012-01-10/

Content Type	Original Author	Original Content on Stackoverflow
Question	Michael Liao	View Question on Stackoverflow
Solution 1 - Mysql	Marc B	View Answer on Stackoverflow
Solution 2 - Mysql	Conrad Frix	View Answer on Stackoverflow
Solution 3 - Mysql	Michael Durrant	View Answer on Stackoverflow
Solution 4 - Mysql	imm	View Answer on Stackoverflow

mysql count group by having

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Solution 3 - Mysql

Solution 4 - Mysql

percentage of two int?

How to flatten array in jQuery?

Attributions