CODEWITHSUNDEEP
sqlite
Smith
Smith

Reputation: 5959

Count from same colum to columns

I am trying to count from customers table customers created today, this month and last month based on the current date, so i tried this

Select * FROM
       (
         (Select count(c1.id) from customers c1 where c1.created = date('now', 'localtime')) d 
         (Select count(id) from customers where created >= date('now','start of month') AND created <= date('now','start of month','+1 month','-1 day')) m
         (Select count(id) from customers where created >= datetime('now', 'start of month') AND created <= date('now', 'localtime')) m
       ) lm

i am expecting somthing like

d   m   lm
-----------
1   20   15

But I can seeem to get it right, what am i doing wrong?

Upvotes: 0

Views: 23

Answers (2)

Cam
Cam

Reputation: 931

Here's a working version of your query:

SELECT d.n as 'Day', m.n as 'Month', lm.n as 'Last Month' FROM
(SELECT count(id) as `n` FROM `customers` 
 WHERE created >= date('now', 'localtime') 
 AND created <= date('now', 'localtime','+1 day')) d,
(SELECT count(id) as `n` FROM `customers`
 WHERE created >= date('now','start of month')
 AND created <= date('now','start of month','+1 month','-1 day')) m,
(SELECT count(id) as `n` FROM `customers`
 WHERE created >= date('now','start of month','-1 month')
 AND created <= date('now','start of month','-1 day')) lm;

Along with a working SQLFiddle with some sample data.

Note that the technique for selecting the current day's created customers will fail, since you're comparing an exact timestamp against today's date. This will never match except for at midnight:

-- Show that checking a DB timestamp against the current
-- date will fail to gather any datapoints (unless the
-- account was created at precisely midnight!)
SELECT count(id) FROM `customers` 
WHERE created = date('now', 'localtime');

Upvotes: 2

vitalygolub
vitalygolub

Reputation: 735

Sqlite 3.7 has many strange bugs 1)count(*) and count(singlefield) return different results in nested queries 2)outer query takes field names not from name of table but from name of field (probably it is even right, but in MSSAL i expect other behavior) and your errors 3)You missed comas between values in select a,b,c 4)lm alias is on wrong place

Your query will be (of course if conditions are right)

Select * FROM
       (
         (Select count(c1.id) d from customers c1 where c1.created = date('now', 'localtime')) ,
         (Select count(id) m from customers where created >= date('now','start of month') AND created <= date('now','start of month','+1 month','-1 day')) ,
         (Select count(id) lm from customers where created >= datetime('now', 'start of month') AND created <= date('now', 'localtime')) 
       )

Upvotes: 0

Related Questions