CODEWITHSUNDEEP
sql
jack rose
jack rose

Reputation: 97

combine the result of the query in sql

I am working with SQL Language. I want to combine the result of the query. Here is my code and expected output.

 create table customer(name varchar,dpt varchar);

insert into customer (name,dpt)VALUES ('albe','cse');
insert into customer (name,dpt)VALUES ('bine','cse');
insert into customer (name,dpt)VALUES ('alfred','ece');
insert into customer (name,dpt)VALUES ('booshan','ece');
insert into customer (name,dpt)VALUES ('antony','eee');
insert into customer (name,dpt)VALUES ('job','ece');

Query 1:

select
dpt,
count(name)
  from customer
  where dpt='ece' or dpt='cse'
    group by dpt;

Result:

| dpt | count |
|-----|-------|
| cse |   2   |
| ece |   3   |

I need the result as:

| cse/ece | 5 |

How it is possible? sqlfiddle is here:

http://sqlfiddle.com/#!12/a1c88/13

Upvotes: 0

Views: 79

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

It seems like you are using PostgreSQL (as can be deduced from sqlfiddle link).

If this is the case, then you can apply array_agg and sum on your query in an outer query:

   SELECT array_agg(dpt), sum(cnt)
   FROM (
      SELECT dpt, count(name) as cnt
      FROM customer
      WHERE dpt='ece' OR dpt='cse'
      GROUP BY dpt) t;

SQL Fiddle Demo

Upvotes: 5

sqluser
sqluser

Reputation: 5672

Also you can use GROUP_CONCAT

SELECT GROUP_CONCAT(dpt), SUM(cnt)
FROM(select
     dpt,
     count(name) cnt
     from customer
     where dpt='ece' or dpt='cse'
     group by dpt) t;

SQL Fiddle

Upvotes: 1

Related Questions