CODEWITHSUNDEEP
sqlpostgresql
Jake Wagner
Jake Wagner

Reputation: 826

Number of Transactions in a given week

I have this column, Actual_Date. Eight transactions took place for the week ending 2/12/2017. I am trying to work on a SQL snippet that would provide me with the number of transactions, which took place the previous week (from Monday 2/6/17 to Sunday 2/12/17). I have tried changing the days to 1,2,3, etc.. and sql still does not provide me with the correct number of transactions which took place. What could I add to my script which would provide me with the number 8?

Actual_Date 
01/31/2017
01/30/2017
01/30/2017
01/30/2017
02/04/2017
02/05/2017
02/06/2017     1
02/10/2017     2
02/08/2017     3
02/06/2017     4
02/12/2017     5
02/06/2017     6
02/06/2017     7
02/12/2017     8     <------------ Number of Transactions

My script:

select coalesce(count(case when Actual_Date = (date_trunc
('week',Actual_Date") + '4 days'::interval)::date
                     then Actual_Date
                      end),0) "This Week"
                      from mysqltable

Upvotes: 0

Views: 1439

Answers (1)

Dale O&#39;Brien
Dale O&#39;Brien

Reputation: 4348

You can do something like this, given the data you provided above;

CREATE TABLE Transactions  (
  id INT PRIMARY KEY ,
  actual_date DATE 
);

INSERT INTO Transactions (id, actual_date) VALUES
  ( 1, '2017-01-31'),
  ( 2, '2017-01-30'),
  ( 3, '2017-01-30'),
  ( 4, '2017-01-30'),
  ( 5, '2017-02-04'),
  ( 6, '2017-02-05'),
  ( 7, '2017-02-06'),
  ( 8, '2017-02-10'),
  ( 9, '2017-02-08'), 
  (10, '2017-02-06'),
  (11, '2017-02-12'),     
  (12, '2017-02-06'),
  (13, '2017-02-06'),   
  (14, '2017-02-12'); `

Execute the following query for Postgresql:

SELECT 
  actual_date as "Acutal_Date",

  extract(week FROM actual_date) as "Week_Number",

  SUM(
   COUNT(*)) 
   OVER (
     PARTITION BY extract(week FROM actual_date) 
     ORDER BY actual_date
   ) as "Weekly_Count"
FROM 
  Transactions 
GROUP BY actual_date
ORDER BY actual_date;

Or, if using MySQL, use:

SELECT 
  actual_date as "Actual_Date",

  WEEKOFYEAR(actual_date) as "Week_Number",

  (SELECT 
     COUNT(*) 
   FROM
     Transactions T
   WHERE
     WEEKOFYEAR(T.actual_date) = WEEKOFYEAR(Transactions.actual_date) AND
     T.actual_date <=  Transactions.actual_date) as "Weekly_Count"
FROM 
  Transactions 
GROUP BY actual_date
ORDER BY actual_date;

Will produce:

┌─────────────┬─────────────┬──────────────┐
│ Acutal_Date │ Week_Number │ Weekly_Count │
├─────────────┼─────────────┼──────────────┤
│ 2017-01-30  │           5 │            3 │
│ 2017-01-31  │           5 │            4 │
│ 2017-02-04  │           5 │            5 │
│ 2017-02-05  │           5 │            6 │
│ 2017-02-06  │           6 │            4 │
│ 2017-02-08  │           6 │            5 │
│ 2017-02-10  │           6 │            6 │
│ 2017-02-12  │           6 │            8 │
└─────────────┴─────────────┴──────────────┘

Upvotes: 2

Related Questions