Order by/ Group by / query help

  • Hi,

    I have a query and want your help with it.

    -----------------Question ------------------------

    Retrieve a report of contact name, sales rep name, the total number of orders per contact and the total value of all orders per contact delivered since midnight this morning.

    -------THis is what I have until now ------------

    SELECT [1contact].name AS CONTACTNAME, [1sales_reps].name AS SALESREPNAME,COUNT([1orders].account_ID)

    FROM [1sales_reps] INNER JOIN

    [1accounts] ON [1sales_reps].sales_rep_id = [1accounts].sales_rep_id INNER JOIN

    [1contact] ON [1accounts].account_id = [1contact].account_ID INNER JOIN

    [1orders] ON [1contact].account_ID = [1orders].account_ID

    where datepart(DD,[1orders].dilivery_date) = DATEPART(dd,getdate())

    group BY [1contact].name,[1sales_reps].name

    --------Below are all the scripts for you -----------

    Schema -

    USE [Liheap2]

    GO

    /****** Object: Table [dbo].[1sales_reps] Script Date: 07/17/2011 17:28:33 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[1sales_reps](

    [sales_rep_id] [int] NOT NULL,

    [name] [varchar](45) NULL,

    [commission_rate] [int] NULL,

    PRIMARY KEY CLUSTERED

    (

    [sales_rep_id] 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

    /****** Object: Table [dbo].[1parts] Script Date: 07/17/2011 17:28:33 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[1parts](

    [part_id] [int] NOT NULL,

    [description] [varchar](45) NULL,

    [price] [varchar](45) NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    /****** Object: Table [dbo].[1orders] Script Date: 07/17/2011 17:28:33 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[1orders](

    [orders_id] [int] NOT NULL,

    [account_ID] [int] NULL,

    [dilivery_date] [datetime] NULL

    ) ON [PRIMARY]

    GO

    /****** Object: Table [dbo].[1order_items] Script Date: 07/17/2011 17:28:33 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[1order_items](

    [orders_items_id] [int] NOT NULL,

    [order_ID] [int] NULL,

    [part_id] [int] NULL,

    [quantity] [int] NULL,

    PRIMARY KEY CLUSTERED

    (

    [orders_items_id] 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

    /****** Object: Table [dbo].[1contact] Script Date: 07/17/2011 17:28:33 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[1contact](

    [contact_id] [int] NOT NULL,

    [account_ID] [int] NULL,

    [name] [varchar](45) NULL,

    [phone] [varchar](45) NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    /****** Object: Table [dbo].[1accounts] Script Date: 07/17/2011 17:28:33 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[1accounts](

    [account_id] [int] NOT NULL,

    [credit_limit] [int] NULL,

    [sales_rep_id] [int] NULL

    ) ON [PRIMARY]

    GO

    ------------Insert statements ------------

    USE [Liheap2];

    SET NOCOUNT ON;

    SET XACT_ABORT ON;

    GO

    BEGIN TRANSACTION;

    INSERT INTO [dbo].[1accounts]([account_id], [credit_limit], [sales_rep_id])

    SELECT 50, 4500, 10 UNION ALL

    SELECT 55, 5000, 10 UNION ALL

    SELECT 60, 5100, 20

    COMMIT;

    RAISERROR (N'[dbo].[1accounts]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;

    GO

    BEGIN TRANSACTION;

    INSERT INTO [dbo].[1contact]([contact_id], [account_ID], [name], [phone])

    SELECT 100, 50, N'tim', N'2172172171' UNION ALL

    SELECT 101, 55, N'Mike', N'7127127121' UNION ALL

    SELECT 102, 60, N'Sid', N'0909090909'

    COMMIT;

    RAISERROR (N'[dbo].[1contact]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;

    GO

    BEGIN TRANSACTION;

    INSERT INTO [dbo].[1order_items]([orders_items_id], [order_ID], [part_id], [quantity])

    SELECT 30, 1, 17, 2 UNION ALL

    SELECT 31, 2, 17, 4 UNION ALL

    SELECT 32, 3, 8, 2 UNION ALL

    SELECT 33, 4, 9, 4

    COMMIT;

    RAISERROR (N'[dbo].[1order_items]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;

    GO

    BEGIN TRANSACTION;

    INSERT INTO [dbo].[1orders]([orders_id], [account_ID], [dilivery_date])

    SELECT 1, 55, '20110101 00:00:00.000' UNION ALL

    SELECT 2, 55, '20110303 00:00:00.000' UNION ALL

    SELECT 3, 50, '20110908 00:00:00.000' UNION ALL

    SELECT 4, 60, '20110703 00:00:00.000'

    COMMIT;

    RAISERROR (N'[dbo].[1orders]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;

    GO

    BEGIN TRANSACTION;

    INSERT INTO [dbo].[1parts]([part_id], [description], [price])

    SELECT 5, N'widget12', N'3000' UNION ALL

    SELECT 6, N'12widget12', N'4000' UNION ALL

    SELECT 5, N'widget12', N'3000' UNION ALL

    SELECT 6, N'12widget12', N'4000' UNION ALL

    SELECT 8, N'spark', N'700' UNION ALL

    SELECT 9, N'laptop', N'7000'

    COMMIT;

    RAISERROR (N'[dbo].[1parts]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;

    GO

    BEGIN TRANSACTION;

    INSERT INTO [dbo].[1sales_reps]([sales_rep_id], [name], [commission_rate])

    SELECT 10, N'Matt', 10 UNION ALL

    SELECT 20, N'Fred', 20 UNION ALL

    SELECT 30, N'Betty', 30

    COMMIT;

    RAISERROR (N'[dbo].[1sales_reps]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;

    GO

    ----------------------------------------------------------------------

  • Sounds like test or homework. (what other reason would exist that you cannot reformat the question?)

    [Edit] hint: in order to find all orders from today you could use something like

    WHERE [1orders].dilivery_date > = DATEADD(dd,DATEDIFF(dd,0,getdate()),0)



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Well. I just lazy to change the formatting. But I have changed it now. To be specific, I wanted to know a way to find a way to get the 'total value of all the orders'. I think using a CTE would do the trick but wanted to advices from the Pros.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply