CODEWITHSUNDEEP
sqlsqlite
user3688227
user3688227

Reputation: 123

Sqlite query - column with sum from first column

Let's say that I have this query

select 
    count(customerid) as Count_Own,
    (select count(customerid) from Customers) Count_ALL 
from 
    Customers
where 
    EmployeeID = 1

It's showing count of customers for employeeid = 1 in first columns and all employees in second column.

Now I want to write similar query by instead of table Customers, I have big subquery:

select 
    count(customerid) as Count_Own,
    (select count(customerid) from /*BIG QUERY*/) Count_ALL 
from 
    ( /*BIG QUERY*/   )
where 
    EmployeeID = 1

I have a problem now with column Count_ALL, because I wanted count this quick, without using my subquery. I wonder if it's simply solution for it.

Upvotes: 1

Views: 192

Answers (1)

dani herrera
dani herrera

Reputation: 51645

Workaround: create a fake column to join both queries:

select 
  count(T1.customerid) as Count_Own, T2.Count_ALL
From ( Select 1 as joinField, --BIG QUERY--   ) T1
join (select count(customerid) Count_ALL, 1 as joinField  
      from customers)  T2
on T1.joinField = T2.joinField
where T1.EmployeeID = 1

Edited due OP comment.

You ca use CTE to avoid write big query for twice. Let's work with this simplified schema:

dh4@GLOW:~/tmp$ sqlite3 pepe.sql
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> create table t ( i int , e int );
sqlite> insert into t values 
   ...> ( 1,1),
   ...> ( 2,1),
   ...> ( 3,1),
   ...> ( 1,2);

Then, your query is:

sqlite> with big_query as
   ...> ( select i,e from t )
   ...> select count(i), (select count(*) from big_query)
   ...> from big_query
   ...> where e=1;
3|4

Also you can use fake join trip to avoid subrogate subquery:

sqlite> with big_query as
   ...> ( select i,e from t ),
   ...> q1 as 
   ...> ( select count(*) as n, 1 as fake  from big_query ),
   ...> q2 as
   ...> ( select count(*) as n, 1 as fake from big_query where e=1)
   ...> select q2.n, q1.n
   ...> from q1 inner join q2
   ...> on q1.fake=q2.fake;
3|4

And the last approach is to use case:

sqlite> select count( case when e = 1 then 1 else null end) , count(*) 
   ...> from t
   ...> ;
3|4

Upvotes: 1

Related Questions