CODEWITHSUNDEEP
sql
package81
package81

Reputation: 21

SQL query that has unconventional calculated column

For some reason my brain can't comprehend this. I have a 'record' table that has columns: id, name, genre, date

I want to do a query that will return every record in the table, but also another column that has the sum of all the records with the same genre and day (of date) and channel (within .0005) as the current record. Hopefully that makes sense. I will try to illustrate:

Table: record

id        name        genre       date          Channel            
1         Scott       rock        2014-01-21    30.0345
2         Jim         rap         2014-01-21    55
3         Dave        country     2014-01-22    23
4         Tim         rock        2014-01-22    25
5         Dave        rock        2014-01-21    34.0350
6         John        rock        2014-03-24    23
7         Stan        rap         2013-09-16    14
8         Jake        country     2014-01-21    30
9         Mike        country     2014-01-22    22.9995
10        Jodi        country     2015-01-22    23.0006
11        Jodi        country     2015-01-22    23.0004

This is what I want my query to return: 

id        name        genre       date        Channel  same_day_count
1         Scott       rock        2014-01-21  30.0345  2 
2         Jim         rap         2014-01-21  55       1 
3         Dave        country     2014-01-22  23       3 
4         Tim         rock        2014-01-22  25       1 
5         Dave        rock        2014-01-21  30.0350  2 
6         John        rock        2014-03-24  23       1 
7         Stan        rap         2013-01-21  14       1 
8         Jake        country     2014-01-21  30       1 
9         Mike        country     2014-01-22  22.9995  3 
10        Jodi        country     2015-01-22  23.0006  1 
11        Jodi        country     2015-01-22  23.0004  3

Please help: this is probably super easy for a SQL expert. Remember the entire timestamp doesn't need to match, i.e. the hour:minute:second. it just needs to be the same yyyy-mm-dd

actually after reviewing this. I think that same_day column is ambiguous. however, the channels are going to differ and be unique enough that I believe it will give accurate results as long as it is actually possible to do this in SQL

Upvotes: 0

Views: 68

Answers (3)

Iłya Bursov
Iłya Bursov

Reputation: 24209

quick idea:

select *,
    (select count(*)
    from table as b
    where a.genre=b.genre and
    cast(a.date as date) = cast(b.date as date)) as same_day_count
from table as a

syntax can vary a little bit across different rdbms

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270773

The standard way of doing this is with window functions. Assuming that your date has no time component, the query would be:

select r.*,
       count(*) over (partition by genre, date) as same_day_count
from record r;

This is ANSI standard functionality and available in most (but not all) databases. If your date has a time component, then you need to remove it. Here are two common ways, depending on the database:

select r.*,
       count(*) over (partition by genre, cast(date as date)) as same_day_count
from record r;

or:

select r.*,
       count(*) over (partition by genre, trunc(date)) as same_day_count
from record r;

Upvotes: 1

Jason Goemaat
Jason Goemaat

Reputation: 29234

You can create a subquery that totals by genre and day and then join that to your record table:

SELECT r.id, r.name, r.genre, r.date, s.recordCount
FROM record r
    INNER JOIN (
        SELECT genre, DATEPART(DAY, date) AS day, COUNT(*) as recordCount
        FROM record
        GROUP Y genre, DATEPART(DAY, date) 
    ) s ON s.genre = r.genre AND s.day = DATEPART(DAY, r.date)

Upvotes: 0

Related Questions