CODEWITHSUNDEEP
sqldatabaseoracle-databasequery-optimization
DazedNConfused
DazedNConfused

Reputation: 199

oracle SQL select from this date_id to this date_id

I have to display data from a table from year 2011-2014. The table has time id to another table called time which stores the actual year. I have the statement to select data from this date id to date id. 

SELECT sum(no_of_placements)
  FROM fact_accounts
 WHERE
  (SELECT account_dim.account_id FROM account_dim WHERE account_dim.account_name = 'DCompany1')
     = fact_accounts.fk2
  AND (
       (SELECT time_dim.time_id FROM time_dim WHERE time_dim.year = 2011)
          = fact_accounts.fk1_time_id
    OR (SELECT time_dim.time_id FROM time_dim WHERE time_dim.year = 2012)
          = fact_accounts.fk1_time_id
    OR (SELECT time_dim.time_id FROM time_dim WHERE time_dim.year = 2013)
          = fact_accounts.fk1_time_id
    OR (SELECT time_dim.time_id FROM time_dim WHERE time_dim.year = 2014)
          = fact_accounts.fk1_time_id
  )
GROUP BY fact_accounts.no_of_placements

How do i achieve the same without setting a condition to match time_id to each year? This does not seem effective incase there is a larger range of years.

Upvotes: 1

Views: 234

Answers (1)

Ken Clubok
Ken Clubok

Reputation: 1238

SELECT sum(no_of_placements)
FROM fact_accounts, account_dim, time_dim 
WHERE account_dim.account_id = fact_accounts.fk2
AND account_dim.account_name = 'DCompany1'  
AND fact_accounts.FK1_TIME_ID = time_dim.time_id
AND time_dim.year BETWEEN 2011 AND 2014

GROUP BY fact_accounts.no_of_placements

or

SELECT sum(no_of_placements)
    FROM fact_accounts,  
    WHERE
    (select account_dim.account_id FROM account_dim WHERE account_dim.account_name = 'DCompany1') = fact_accounts.fk2

AND fact_accounts.FK1_TIME_ID IN
(select time_dim.time_id from time_dim WHERE 
time_dim.year BETWEEN 2011 AND 2014)

GROUP BY fact_accounts.no_of_placements

But why are you both summing by and grouping by no_of_placements? That makes little sense to me.

Upvotes: 2

Related Questions