CODEWITHSUNDEEP
sqldate
CelineDion
CelineDion

Reputation: 1088

How to filter by date in SQL

I'm doing an SQL problem wherein I need to find out how many 'units' of the best-selling genre of music were sold in the year 2008 for some music store. There are a bunch of tables, but so far I was able to string together most of the information I need.

select 
    music_store.invoice_lines.invoice_id, 
    music_store.invoice_lines.track_id, 
    music_store.invoice_lines.quantity, 
    music_store.invoices.invoice_date, 
    music_store.invoices.id, 
    music_store.tracks.genre_id
from 
    music_store.invoice_lines
inner join
    music_store.invoices on music_store.invoice_lines.invoice_id = music_store.invoices.id
inner join
   music_store.tracks on music_store.tracks.id = music_store.invoice_lines.track_id;

From here, I need to filter out all of the invoice dates that do not end in 2008, and then find the mode for music_store.tracks.genre_id, find the corresponding genre (which is another table), and then sum all of the "quantity" values.

How do I do this? I'm pretty lost and also an absolute beginner to SQL and more-or-less a beginner to programming in general.

EDIT: I don't know what DBMS I'm using, presumably the most standard version of SQL? I'm running SQL on Wagon fwiw

Upvotes: 0

Views: 429

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35583

The most generic, and likely best performing ("sargable") approach is to simply specify 2 dates and then choose the records within the boundaries set by those dates.

  WHERE invoices.invoice_date >= '2008-01-01'
  AND invoices.invoice_date < '2009-01-01'

Using the >= with < is deliberate and it allows the column being evaluated to be of any time unit precision. Also by avoiding functions on the data the dbms can use indexes on the date column plus avoid the calculation of each function too.

As you don't specify which dbms you are using I can't predict how you need to specify those date literals. You nay need to use to_date() or str_to_date() as examples

MySQL

  WHERE invoices.invoice_date >= str_to_date('2008-01-01','%Y-%m-%d')
  AND invoices.invoice_date < str_to_date('2009-01-01','%Y-%m-%d')

Oracle

  WHERE invoices.invoice_date >= to_date('2008-01-01','yyyy-mm-dd')
  AND invoices.invoice_date < to_date('2009-01-01','yyyy-mm-dd')

T-SQL

  WHERE invoices.invoice_date >= '20080101'
  AND invoices.invoice_date < '20090101'

Please also note that the actual data type for the column invoices.invoice_date will influence what functions are applicable, and that I have assumed that this column is NOT a string that looks like a date/time value.

Upvotes: 1

gr1zzly be4r
gr1zzly be4r

Reputation: 2152

Without knowing the specific RDBMS that you're using, the SQL code will look something like this to get the mode of the genre_id. You could then join in the genre table to get the genre name. Hope that this helps. 

select music_store.tracks.genre_id, COUNT(1) genre_count
from music_store.invoice_lines

INNER JOIN music_store.invoices
on music_store.invoice_lines.invoice_id = music_store.invoices.id
INNER JOIN music_store.tracks 
on music_store.tracks.id = music_store.invoice_lines.track_id

WHERE YEAR(music_store.invoices.invoice_date) = 2008
GROUP BY music_store.tracks.genre_id
ORDER BY genre_count DESC 
LIMIT 1;

Upvotes: 1

Related Questions