I have the following table:

key	keytype	elements
key1	AB	7
key2	CD	3
key2	CD	2
key3	AB	4
key3	AB	3
key3	AB	1

And I'm using this code:

select key,
  count(*) as rcount,
  sum(elements) as ecount
from keyhistory
group by key
order by key

The result looks like this:

key	rcount	ecount
key1	1	7
key2	2	5
key3	3	8

The problem is that I also need to show the column keytype from the first table on the second table. A key will always have same keytype (so all key1 occurrences will be AB, all key2 occurrences will be CD, and so on). I want to merge the keytype rows in a single row respective to their 'key', like I did with elements in ecount, like the following example:

key	rcount	ecount	keytype
key1	1	7	AB
key2	2	5	CD
key3	3	8	AB

Do SQL have any function or syntax which I can use to reach the result of the last table using the values of the original table?

CodePudding user response：

Just list the keytype in the select and the group by (in your original query)

select key,keytype,
  count(*) as rcount,
  sum(elements) as ecount
from keyhistory
group by key,keytype
order by key

CodePudding user response：

GROUP_CONCAT could solve this.

select key,
  count(*) as rcount,
  sum(elements) as ecount,
  GROUP_CONCAT(keytype)
from keyhistory
group by key
order by key

SQL Server use STRING_AGG instead.

If you have unique value for keytype, SQLite default allowing query directly: SELECT keytype FROM test GROUP BY key, but it also allow GROUP_CONCAT

Or instead group it directly:

select key,
  count(*) as rcount,
  sum(elements) as ecount,
  keytype
from keyhistory
group by key, keytype
order by key

CodePudding user response：

INNER JOIN, an alias, and DISTINCT clause should do it,

SELECT DISTINCT
    [KeyStats].*,
    [keyhistory].keytype
FROM (
    SELECT 
        [key],
        count(*) as rcount,
        sum(elements) as ecount
    FROM keyhistory
    GROUP BY [key]
    ORDER BY [key]
) as KeyStats
INNER JOIN keyhistory ON keyhistory.[key] = KeyStats.[key]