CODEWITHSUNDEEP
sqlruby-on-rails
dneumark
dneumark

Reputation: 286

group + count with different column types

I have a customer model which represents customers and their SINGLE purchase at the shop at a certain date.

For example, if Customer1 came in at 1/1 and bought an Basketball. While Customer2 came at 2/1 and bought a Doll, the model will look like this:

|----------------|------------|------------------|------------|
|    Customer    |    Date    |       Item       |    Name    |
|----------------|------------|------------------|-------------
|       1        |    1/1     |    Basketball    |     Bu     |
|----------------|------------|------------------|-------------
|       2        |    2/1     |       Doll       |     Hu     |
|----------------|------------|------------------|-------------

Now, If I wished to fetch a table the who is grouped by date, that sums up all of the bought items at a certain day, I will write something like this:

customers.group("DATE_FORMAT(date(started_at), '%d-%m')").count

That will give me back a table which looks like this:

|------------|-------------|
|    Date    |    Count    |
|------------|-------------|
|    1/1     |      1      |
|------------|-------------|
|    2/1     |      1      |
|------------|-------------|

But what If I would like to have a count of each item at each single day, PLUS the sum of all items in the shop? How would I write it in rails for getting the following output?

|------------|------------------|------------|-----------|
|    Date    |    Basketball    |    Doll    |    Sum    |
|------------|------------------|------------|-----------|
|    1/1     |        1         |      0     |     1     |
|------------|------------------|------------|-----------|
|    2/1     |        0         |      1     |     1     |
|------------|------------------|------------|-----------|

Upvotes: 0

Views: 70

Answers (1)

Teja
Teja

Reputation: 13534

SELECT Date,
       SUM( CASE WHEN Item = 'Basketball' THEN 1 ELSE 0 END ) AS BasketBall,
       SUM( CASE WHEN Item = 'Doll' THEN 1 ELSE 0 END ) AS Doll,
       COUNT(1) AS Totalsum 
  FROM Customers
GROUP BY Date;

Upvotes: 3

Related Questions