SELECT with a GROUP BY column

countgroup byselect

The situation is like this:

I have a table that has one row for each copy of a letter, which also contains the letter ID. I want a single query that outputs each letter copy row with the letter copy ID, letter ID and count of all copies for the letter ID …

Table Rows

Letter Copy ID      Letter ID
1                   1
2                   1
3                   1
4                   2
5                   2
6                   3

Expected Output

Letter Copy ID      Letter ID      Letter Copy Count
1                   1              3
2                   1              3
3                   1              3
4                   2              2
5                   2              2
6                   3              1

Best Answer

If you don't have access to window-functions, you can use a subquery:

SELECT Letter_Copy_ID
     , Letter_ID
     , ( SELECT COUNT(1) 
         FROM T AS T2
         WHERE T1.Letter_ID = T2.Letter_ID ) AS Letter_Copy_Count
FROM T AS T1

If you have access to window functions:

SELECT Letter_Copy_ID
     , Letter_ID
     , COUNT(1) OVER (PARTITION BY Letter_ID) AS Letter_Copy_Count
FROM T

You should avoid space in your identifiers (I used _ in my example), but if you feel you have to use them, you can quote them with ", as in "Letter Copy Count". Note that this makes the identifier case sensitive, so you will have to use the exact spelling.

Related Solutions

Mysql – How to select specific rows if a column exists or all rows if a column doesn’t

My first thought would be to use the INFORMATION_SCHEMA first, so you get to know (in one query for all tables in the MySQL instance) which tables have an active column and then use that info to construct your queries. And this is probably the most sane approach.

There is one other, tricky way though that works no matter if the table has or not such a column:

SELECT 
  ( SELECT COUNT(*)
    FROM TableName AS t
    WHERE active = 1
  ) AS cnt
FROM
  ( SELECT 1 AS active
  ) AS dummy ;

Tested at SQL-Fiddle How it works?

If the table has a column named active, the query is "translated" as if it had:

    WHERE t.active = 1

If the table doesn't have a column named active, the query is "translated" as if it had:

    WHERE dummy.active = 1         -- which is true

Mysql – Pivot table with MySQL

Give this a try,tested on my machine

First make sure GROUP_CONCAT has plenty of space:

SET SESSION group_concat_max_len = 1000000;

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(DISTINCT CASE WHEN buyer_country= ''',
      buyer_country,
      ''' THEN transactions.buyer_id END) AS ',
      buyer_country
    )
   )INTO @sql
FROM  transactions 
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id;


SET @sql=CONCAT('SELECT transactions.seller_country,',@sql,' FROM  transactions 
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id GROUP BY transactions.seller_country');



PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Link

Edit

ALTER TABLE transactions ADD key trans_idx (buyer_id)
ALTER TABLE buyers ADD key buyers_idx (buyer_id)

Best Answer

Related Solutions

Mysql – How to select specific rows if a column exists or all rows if a column doesn’t

Mysql – Pivot table with MySQL

Related Question