# Equivalent of SQL Union in KSQLDB
**URL:** https://forum.confluent.io/t/equivalent-of-sql-union-in-ksqldb/5943
**Category:** ksqlDB
**Created:** [1 September 2022 14:28 UTC](https://forum.confluent.io/t/equivalent-of-sql-union-in-ksqldb/5943 "2022-09-01T14:28:36Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@n.kant](https://forum.confluent.io/u/n.kant)
#### Post date: [1 September 2022 14:28 UTC](https://forum.confluent.io/t/equivalent-of-sql-union-in-ksqldb/5943/1 "2022-09-01T14:28:37Z")
Hello,
I have a requirement to create a table by creating multiple aggregates on a KSQL stream.
Eg.
CREATE TABLE AGGREGATES AS
SELECT GENDER AS ATTRIBUTE, COUNT(_) AS COUNTS FROM PERSON\_STREAM GROUP BY GENDER
UNION
SELECT CITY AS ATTRIBUTE, COUNT(_) AS COUNTS FROM PERSON\_STREAM GROUP BY CITY
I am not able to figure out how something like this be achieved in KSQLDB. I do not want to create an separate table for each attribute. Thanks a lot in advance.
---
### Author:  [@gianlucanatali](https://forum.confluent.io/u/gianlucanatali)
#### Post date: [2 September 2022 12:52 UTC](https://forum.confluent.io/t/equivalent-of-sql-union-in-ksqldb/5943/2 "2022-09-02T12:52:00Z")
Hi @n.kant I think one way to solve this is to create your “aggregated” stream firs, and use the `INSERT INTO SELECT`syntax from all the different queries you wish to UNION. The you can run your create table with group by… Have a look at this tutoria: [How to merge many streams into one stream using ksqlDB](https://developer.confluent.io/tutorials/merge-many-streams-into-one-stream/ksql.html)
Let us know how it goes!
---
### Author:  [@n.kant](https://forum.confluent.io/u/n.kant)
#### Post date: [5 September 2022 20:14 UTC](https://forum.confluent.io/t/equivalent-of-sql-union-in-ksqldb/5943/3 "2022-09-05T20:14:56Z")
Hi Gianluca,
Thanks a lot for coming back to me on this. I just though of bringing in a simple example to demonstrate what we are trying to achieve.
SELECT \* FROM SAMPLESTREAM EMIT CHANGES;
EMPID EMPNAME EMPCITY EMPDEPARTMENT
1 Peter London Dev
1 Sam London Dev
3 Nick London Dev
4 Arthur Oxford Test
4 Dave Oxford Test
5 Michelle Oxford Test
6 Ramesh Oxford Test
7 Mariam Oxford Test
what we want to achieve in a single KSQLDB table is:
ATTRIBUTE\_TYPE ATTRIBUTE\_NAME COUNTS
CITY London 3
CITY Oxford 5
DEP Dev 3
DEP Test 5
In a typical database env we will do
SELECT ‘CITY’ AS ATTRIBUTE\_TYPE , EMPCITY AS ATTRIBUTE\_NAME, COUNT(_) AS COUNTS FROM SAMPLESTREAM GROUP BY EMPCITY
UNION
SELECT ‘DEPT’ AS ATTRIBUTE\_TYPE , EMPDEPARTMENT AS ATTRIBUTE\_NAME, COUNT(_) AS COUNTS FROM SAMPLESTREAM GROUP BY EMPDEPARTMENT
I am bale to create two separate KSQL tables
CREATE TABLE CITY\_COUNT
AS
SELECT ‘CITY’ AS ATTRIBUTE\_TYPE
,EMPCITY AS ATTRIBUTE\_NAME
,COUNT(\*) AS COUNTS
FROM SAMPLESTREAM GROUP BY EMPCITY
EMIT CHANGES;
Results:
ATTRIBUTE\_TYPE ATTRIBUTE\_NAME COUNTS
CITY London 3
CITY Oxford 5
and
CREATE TABLE DEPT\_COUNT
AS
SELECT ‘DEPT’ AS ATTRIBUTE\_TYPE
,EMPDEPARTMENT AS ATTRIBUTE\_NAME
,COUNT(\*) AS COUNTS
FROM SAMPLESTREAM GROUP BY EMPDEPARTMENT
EMIT CHANGES;
Result:
ATTRIBUTE\_TYPE ATTRIBUTE\_NAME COUNTS
DEP Dev 3
DEP Test 5
Based on your suggestion not sure how can I create an aggregated stream from stream SAMPLESTEAM as when I try TO CREATE an aggregated stream using the query below:
CREATE STREAM CITY\_COUNT\_STREAM
AS
SELECT ‘CITY’ AS ATTRIBUTE\_TYPE
,EMPCITY AS ATTRIBUTE\_NAME
,COUNT(\*) AS COUNTS
FROM SAMPLESTREAM GROUP BY EMPCITY
EMIT CHANGES;
I get the error
“Could not determine output schema for query due to error: Invalid result type. Your SELECT query produces a TABLE. Please use CREATE TABLE AS SELECT statement instead.”
Look forward to hearing from you.
---
### Author:  [@gianlucanatali](https://forum.confluent.io/u/gianlucanatali)
#### Post date: [14 September 2022 12:33 UTC](https://forum.confluent.io/t/equivalent-of-sql-union-in-ksqldb/5943/4 "2022-09-14T12:33:58Z")
Hi @n.kant !
One way to do this is to create first the stream (without the group by) and then create a table out of that stream with the group by?SMG like:
CREATE STREAM all\_attrs (ATTRIBUTE\_TYPE VARCHAR, ATTRIBUTE\_NAME VARCHAR)
WITH (kafka\_topic=‘all\_attrs’, partitions=3, value\_format=‘avro’);
INSERT INTO all\_attrs SELECT ‘CITY’ AS ATTRIBUTE\_TYPE, EMPCITY AS ATTRIBUTE\_NAME FROM SAMPLESTREAM ;
INSERT INTO all\_attrs SELECT ‘DEP’ AS ATTRIBUTE\_TYPE, EMPNAME AS ATTRIBUTE\_NAME FROM SAMPLESTREAM ;
THEN you can create the table with the counts:
CREATE TABLE COUNT\_BY\_ATTRTYPE
AS
SELECT ATTRIBUTE\_TYPE
,ATTRIBUTE\_NAME
,COUNT(\*) AS COUNTS
FROM all\_attrs GROUP BY ATTRIBUTE\_TYPE, ATTRIBUTE\_NAME
EMIT CHANGES;
I didn’t test the code, so there could very well be typos 🙂 Let us know how it goes!
---
### Author:  [@system](https://forum.confluent.io/u/system)
#### Post date: [1 October 2022 14:28 UTC](https://forum.confluent.io/t/equivalent-of-sql-union-in-ksqldb/5943/5 "2022-10-01T14:28:51Z")
This topic was automatically closed after 30 days. New replies are no longer allowed.