Join Query
-
Tables :
CREATE TABLE MYSTORES ( STOREID int) contains 29, 35,1
CREATE TABLE SALES(
Storeid int not null,
Sales decimal,
Guest int,
BusinessDate Datetime
)
My Basic Query:
Select StoreId as StoreId
Sales as CurrSales,
GuestCount as CurrGuestCount
from Sales where StoreID in (select StoreId from mystores)
and BusinessDate = '5/31/2009'
Select StoreId as StoreId,
Sales as LastYearSales,
GuestCount as LastYearGuestCount
from Sales
where StoreId in (select StoreId from mystores)
and BusinessDate = '5/30/2008'
I would like to expand to include weekly totals although Weekly columns are not in diagram:
Select Sum(Sales) as CurrWTDSales,
sum(GuestCount) as CurrWTDGuestCount,
from Sales
where StoreID in (select StoreId from mystores)
and BusinessDate between '5/22/2009' and '5/31/2009'
Select Sales as LastYearWTDSales,
GuestCount as LastYearWTDGuestCount,
from SALES
where StoreID in (select StoreId from mystores)
and BusinessDate between '5/21/2008' and '5/30/2009'
When I try to use this:
Select rs.storeid
, Curr.Sales
, Curr.GuestCount
, LastYr.GuestCount
. LastYr.Sales
, WTD.Sales
, WTD.GuestCount
from report_stores mystores
JOIN Sales Curr ON (Curr.storeid = rs.storeid)
where Sales.businessDate = '5/31/2009'
JOIN Sales LastYr ON (LastYr.storeid = rs.storeid)
where LastYr.businessDate = '5/30/2008'
JOIN Sales WTD ON (WTD.storeid = rs.storeid)
where WTD.businessDate between '5/22/2009' and '5/30/2009' ;
I get "NOT GROUP FUNCTIONS" error for non aggregate columns. I don't want to have to list all in the group by clause.
I hope the resulting query can be used in Oracle too. I can easily use temptables in SQL but Oracle is not as clear.
-
DDL/DML
Using SQL Server 2005 on VISTA
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='MYSALES')
DROP TABLE MYSALES
GO
CREATE TABLE [dbo].[MYSALES](
[Storeid] [int] NOT NULL,
[Sales] [decimal](18, 0) NULL,
[Guest] [int] NULL,
[BusinessDate] [datetime] NULL
) ON [PRIMARY]
INSERT INTO [MYSALES]([Storeid],[Sales],[Guest],[BusinessDate])VALUES(1,100,50,'May 31 2009 12:00:00:000AM')
INSERT INTO [MYSALES]([Storeid],[Sales],[Guest],[BusinessDate])VALUES(29,200,60,'May 31 2009 12:00:00:000AM')
INSERT INTO [MYSALES]([Storeid],[Sales],[Guest],[BusinessDate])VALUES(35,300,40,'May 31 2009 12:00:00:000AM')
INSERT INTO [MYSALES]([Storeid],[Sales],[Guest],[BusinessDate])VALUES(1,100,50,'May 30 2009 12:00:00:000AM')
INSERT INTO [MYSALES]([Storeid],[Sales],[Guest],[BusinessDate])VALUES(29,200,60,'May 30 2009 12:00:00:000AM')
INSERT INTO [MYSALES]([Storeid],[Sales],[Guest],[BusinessDate])VALUES(35,300,40,'May 30 2009 12:00:00:000AM')
INSERT INTO [MYSALES]([Storeid],[Sales],[Guest],[BusinessDate])VALUES(1,150,50,'May 30 2008 12:00:00:000AM')
INSERT INTO [MYSALES]([Storeid],[Sales],[Guest],[BusinessDate])VALUES(29,250,50,'May 30 2008 12:00:00:000AM')
INSERT INTO [MYSALES]([Storeid],[Sales],[Guest],[BusinessDate])VALUES(35,350,50,'May 30 2008 12:00:00:000AM')
INSERT INTO [MYSALES]([Storeid],[Sales],[Guest],[BusinessDate])VALUES(1,150,50,'May 29 2008 12:00:00:000AM')
INSERT INTO [MYSALES]([Storeid],[Sales],[Guest],[BusinessDate])VALUES(29,250,50,'May 29 2008 12:00:00:000AM')
INSERT INTO [MYSALES]([Storeid],[Sales],[Guest],[BusinessDate])VALUES(35,350,50,'May 29 2008 12:00:00:000AM')
---========== MYSTORES
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='MYSTORES')
DROP TABLE MYSTORES
GO
CREATE TABLE [dbo].[MYSTORES](
[STOREID] [int] NULL
) ON [PRIMARY]
go
INSERT INTO [MyStores] ([STOREID])VALUES(1)
INSERT INTO [MyStores] ([STOREID])VALUES(25)
INSERT INTO [MyStores] ([STOREID])VALUES(39)
go
Select curr.StoreId as StoreId,
Curr.Sales as CurrSales,
LastYear.Sales as LastYearSales,
Curr.Sales - LastYear.Sales as DiffInSales,
Curr.Guest as CurrGuestCount,
LastYear.Guest as LastYearGuestCount,
curr.guest - lastyear.guest as diffInGuestCount
from mystores stores
inner join mysales Curr on curr.storeid = stores.storeid
and curr.BusinessDate = '5/31/2009'
inner join mysales LastYear on LastYEar.storeid = stores.storeid
and LastYear.BusinessDate = '5/30/2008'
where stores.StoreID in (select StoreId from mystores)
This incorrectly returns only 1 row instead of 3
Then I want to incorporate this:
JOIN mysales WTD ON WTD.Storeid = rpt.storeid
AND WTD.BusinessDate between '5/30/2009'and '5/31/2009'
JOIN mysales WTDLy ON curr.StoreId= rpt.storeid
AND WTDLy.BusinessDate between '5/29/2008'and '5/30/2008'
I get Not Group Function error
-
hi,
Your first query
Select curr.StoreId as StoreId,
Curr.Sales as CurrSales,
LastYear.Sales as LastYearSales,
Curr.Sales - LastYear.Sales as DiffInSales,
Curr.Guest as CurrGuestCount,
LastYear.Guest as LastYearGuestCount,
curr.guest - lastyear.guest as diffInGuestCount
from mystores stores
inner join mysales Curr on curr.storeid = stores.storeid
and curr.BusinessDate = '5/31/2009'
inner join mysales LastYear on LastYEar.storeid = stores.storeid
and LastYear.BusinessDate = '5/30/2008'
where stores.StoreID in (select StoreId from mystores)
this even works fine...
U just check your input values...
U include this two values in the table MYSTORES.
INSERT INTO [MYSTORES] ([STOREID])VALUES(35)
INSERT INTO [MYSTORES] ([STOREID])VALUES(29)
it returns three rows at result
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply