Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » First Post with a very basic query request
Add to briefcase

First Post with a very basic query request Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
blackhawkpowers
Posted Friday, December 07, 2012 10:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 10, 2012 1:06 PM
Points: 3, Visits: 10
Hello all but i'm trying to make a very basic query and i'll give an example of the db here but what I have is a table basically with some data such as ordernum, apples, oranges, bananas, orderdate. And what I would like to have a summary view where each row would be a month Jan, Feb, March, then we would have columns where it would give the sum of the number of apples purchased in Jan etc.

So the DB would look like

ordernum apples oranges bananas orderdate
1 1 1 0 '2012-1-14'
2 1 0 5 '2012-3-1'
3 1 1 1 ' 2012-4-22'


then the report would return

month apples oranges bananas
Jan 1 1 0
Feb 0 0 0
Mar 1 0 5
Apr 1 1 1



hopefully it's as simple as I think but i'm new to this stuff so any help is greatly appreciated.
Post #1394302
Jason-299789
Posted Saturday, December 08, 2012 4:16 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:30 AM
Points: 803, Visits: 2,124
Welcome to SSC.

The Query is fairly straight forward, all you're doing is grouping and summing the data up by the Month so this should work.

Select 
	DateName(Month,OrderDate) [Month]
	,Sum(Apples) as TotalApples
	,Sum(Oranges) as TotalOranges
	,Sum(Bananas) as TotalBananas
From 
	[Order]
Group by 
	DateName(Month,OrderDate)

There are a few flaws with this in that if you have mutiple years then you also need to provide a Year to distinguish monthly sales between different years.

You may also want to consider normalising the table a little by adding Product and OrderLine Tables t to the DB, so that you can scale out easier, eg something like this

CREATE TABLE #Order (
	OrderNumber Int NOT NULL PRIMARY KEY IDENTITY(1,1) NOT FOR REPLICATION 
	,OrderDate Int NOT NULL
)

CREATE TABLE #OrderLine(
	OrderLineNumber Int NOT NULL PRIMARY KEY Identity(1,1) NOT FOR REPLICATION  
	,OrderNumber Int NOT NULL --FK to Order column
	,ProductId Int NOT NULL -- FK to Product
	,OrderQty Int NOT NULL
	,OrderPrice Decimal(18,2) NOT NULL
)

CREATE TABLE #Product(
	ProductId Int NOT NULL PRIMARY KEY Identity(1,1) NOT FOR REPLICATION 
	,ProductName varchar(100) NOT NULL 
	,ProductPrice Decimal(18,2)
)

I would suggest buying a book on database design and normalisation for more details on best practices.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1394317
blackhawkpowers
Posted Saturday, December 08, 2012 10:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 10, 2012 1:06 PM
Points: 3, Visits: 10
Thank you so much for your response, I relized you answered exactly what I asked but what I asked was close to what I wanted but a little to different to work with. So the background on this, it's not my DB but i'm trying to do some custom reporting off of it. so I'll show what I do have and you were absolutely right i'm going to need to get the year and month as well for some ordering and it is a multi-year db. I'm also going to post 5 rows of the data from this db (obviously it's not my db so I can't modify the db itself) and some of these DBs do have upwards of 50,000 rows.

Select
DateName(Month,dateopened) [Month],
(select Count(workorderid) from workorder where pmprojectid is null and Archived=0 and IsDeleted=0) as WOs,
(select Count(workorderid) from workorder where pmprojectid>0 and Archived=0 and IsDeleted=0) as PMs
From
workorder
Group by
DateName(Month, DateOpened)


there's the current query and here's some of the data...

WorkOrderID WorkOrderIdentifier DateOpened DateClosed PMProjectID NoteIdentifier Archived IsDeleted
465 12_002687 2012-08-02 02:14:59.000 2012-08-11 01:53:21.000 NULL 8236 0 0
466 12_002688 2012-08-02 06:00:41.000 2012-09-11 17:51:55.000 14 8239 0 0
467 12_002689 2012-08-02 06:00:43.000 2012-09-11 17:50:47.000 14 8242 0 0
468 12_002690 2012-08-02 06:00:44.000 2012-09-11 17:49:36.000 14 8245 0 0
469 12_002691 2012-08-02 06:00:45.000 2012-09-11 17:48:08.000 14 8248 0 0
Post #1394338
Jeff Moden
Posted Saturday, December 08, 2012 6:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 32,906, Visits: 26,792
blackhawkpowers (12/8/2012)
there's the current query and here's some of the data...

WorkOrderID WorkOrderIdentifier DateOpened DateClosed PMProjectID NoteIdentifier Archived IsDeleted
465 12_002687 2012-08-02 02:14:59.000 2012-08-11 01:53:21.000 NULL 8236 0 0
466 12_002688 2012-08-02 06:00:41.000 2012-09-11 17:51:55.000 14 8239 0 0
467 12_002689 2012-08-02 06:00:43.000 2012-09-11 17:50:47.000 14 8242 0 0
468 12_002690 2012-08-02 06:00:44.000 2012-09-11 17:49:36.000 14 8245 0 0
469 12_002691 2012-08-02 06:00:45.000 2012-09-11 17:48:08.000 14 8248 0 0


Howdy!

Welcome aboard! If you'll take the time to post readily consumable data IAW the first link in my signature line below, you'll likely get tested code back and get it very quickly.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1394369
blackhawkpowers
Posted Sunday, December 09, 2012 6:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 10, 2012 1:06 PM
Points: 3, Visits: 10
thanks for the pointers so here's what we've got now based on your recommendations


/****** Object: Table [dbo].[WorkOrder] Script Date: 12/09/2012 16:21:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[WorkOrder](
[WorkOrderID] [bigint] IDENTITY(1,1) NOT NULL,
[WorkOrderIdentifier] [varchar](255) NOT NULL,
[DateClosed] [datetime] NULL,
[DateOpened] [datetime] NULL,
[PMProjectID] [bigint] NULL,
[IsDeleted] [bit] NOT NULL,
[Archived] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
[WorkOrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[WorkOrder] WITH CHECK ADD CONSTRAINT [FK_WorkOrder_PMProject] FOREIGN KEY([PMProjectID])
REFERENCES [dbo].[PMProject] ([PMProjectID])
GO

ALTER TABLE [dbo].[WorkOrder] CHECK CONSTRAINT [FK_WorkOrder_WorkOrder]
GO

ALTER TABLE [dbo].[WorkOrder] ADD CONSTRAINT [DF_WorkOrder_IsDeleted] DEFAULT ((0)) FOR [IsDeleted]
GO

ALTER TABLE [dbo].[WorkOrder] ADD CONSTRAINT [DF__WorkOrder__Archi__105BFE92] DEFAULT ((0)) FOR [Archived]
GO




and the insert
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Workorder ON

--===== Insert the test data into the test table
INSERT INTO #Workorder
(WorkorderID, WorkorderIdentifier, DateOpened, DateClosed, Archived, IsDeleted, PMProjectID)
SELECT '999','12_003221','Oct 1 2012 6:00AM','Oct 3 2012 12:10PM','0','0','22' UNION ALL
SELECT '998','12_003220','Oct 1 2012 6:00AM','Oct 2 2012 8:28PM','0','0','22' UNION ALL
SELECT '997','12_003219','Oct 1 2012 6:00AM','Oct 2 2012 7:37PM','0','0','22' UNION ALL
SELECT '996','12_003218','Oct 1 2012 6:00AM','Oct 2 2012 7:34PM','0','0','22' UNION ALL
SELECT '995','12_003217','Oct 1 2012 6:00AM','Oct 2 2012 7:25PM','0','0','22' UNION ALL
SELECT '994','12_003216','Oct 1 2012 6:00AM','Oct 2 2012 7:23PM','0','0','22' UNION ALL
SELECT '993','12_003215','Oct 1 2012 6:00AM','Oct 9 2012 3:56PM','0','0','23' UNION ALL
SELECT '992','12_003214','Oct 1 2012 6:00AM','Oct 9 2012 8:09PM','0','0','23' UNION ALL
SELECT '991','12_003213','Oct 1 2012 6:00AM','Oct 10 2012 6:47PM','0','0','23' UNION ALL
SELECT '990','12_003212','Oct 1 2012 6:00AM','Oct 8 2012 5:02PM','0','0','23' UNION ALL
SELECT '989','12_003211','Oct 1 2012 6:00AM','Oct 8 2012 4:56PM','0','0','23' UNION ALL
SELECT '988','12_003210','Oct 1 2012 6:00AM','Oct 8 2012 2:37PM','0','0','17' UNION ALL
SELECT '985','12_003207','Sep 30 2012 6:00AM','Oct 2 2012 3:18PM','0','0','1' UNION ALL
SELECT '984','12_003206','Sep 30 2012 6:00AM','Oct 2 2012 3:16PM','0','0','1' UNION ALL
SELECT '966','12_003188','Sep 28 2012 5:00AM','Oct 1 2012 4:21PM','0','0','2' UNION ALL
SELECT '908','12_003130','Sep 25 2012 5:00AM','Sep 27 2012 4:10PM','0','0','16' UNION ALL
SELECT '907','12_003129','Sep 25 2012 5:00AM','Sep 27 2012 4:10PM','0','0','16' UNION ALL
SELECT '906','12_003128','Sep 25 2012 5:00AM','Sep 27 2012 4:10PM','0','0','16' UNION ALL
SELECT '905','12_003127','Sep 25 2012 5:00AM','Sep 27 2012 4:10PM','0','0','16' UNION ALL
SELECT '904','12_003126','Sep 25 2012 5:00AM','Sep 27 2012 4:10PM','0','0','16' UNION ALL
SELECT '903','12_003125','Sep 25 2012 5:00AM','Sep 27 2012 4:10PM','0','0','16' UNION ALL
SELECT '902','12_003124','Sep 25 2012 5:00AM','Sep 25 2012 3:08PM','0','0','1' UNION ALL
SELECT '901','12_003123','Sep 25 2012 5:00AM','Sep 25 2012 3:12PM','0','0','1' UNION ALL
SELECT '900','12_003122','Sep 25 2012 5:00AM','Sep 27 2012 4:10PM','0','0','18' UNION ALL
SELECT '899','12_003121','Sep 25 2012 5:00AM','Sep 27 2012 4:11PM','0','0','18' UNION ALL
SELECT '884','12_003106','Sep 24 2012 6:00AM','Sep 27 2012 4:11PM','0','0','22' UNION ALL
SELECT '883','12_003105','Sep 24 2012 6:00AM','Sep 27 2012 4:11PM','0','0','22' UNION ALL
SELECT '882','12_003104','Sep 24 2012 6:00AM','Sep 27 2012 4:11PM','0','0','22' UNION ALL
SELECT '881','12_003103','Sep 24 2012 6:00AM','Sep 27 2012 4:11PM','0','0','22'


--===== Set the identity insert back to normal
SET IDENTITY_INSERT #Workorder ON



Post #1394428
Jason-299789
Posted Monday, December 10, 2012 12:49 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:30 AM
Points: 803, Visits: 2,124
Thanks for the table scripts though you dont need the FK as we dont have the table it references.

You original query wont work as this sums all rows, regardless of period, so you also need to add a filter to restrict the query on period.

The following query will give you the correct totals.

Select 
DateName(Month,dateopened) [Month],
 (select Count(workorderid) from #workorder 
	where pmprojectid is null 
		and Archived=0 
		and IsDeleted=0 
		AND DateName(Month,dateopened)=DateNAme(Month,wo.dateopened)) as WOs,
 (select Count(workorderid) 
	from #workorder 
	where pmprojectid>0 
		and Archived=0 
		and IsDeleted=0
		AND DateName(Month,dateopened)=DateNAme(Month,wo.dateopened)) as PMs
From 
#workorder wo
Group by 
DateName(Month, DateOpened)


However it can also be re-written to run without the co-related Sub-queries, simply by doing this

Select 
	DateName(Year,dateopened) Year
	,DateName(Month,dateopened) Month
	,SUM(CASE when pmprojectid is null and Archived=0 and IsDeleted=0 Then 1 Else 0 end) WOs
	,SUM(CASE when pmprojectid>0 and Archived=0 and IsDeleted=0Then 1 Else 0 end) PMs
From #WorkOrder
Group by
	DateName(Year,dateopened)
	,DateName(Month,dateopened)

Regards.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1394470
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.