Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Query - statistic report

Author  Topic 

arhicoc
Starting Member

2 Posts
Posted - 2009-02-05 : 01:12:08
Two tables


1. Distributors (id, name)

  • 1 Carrie Bradshaw

  • 2 AnnaLynne McCord

  • 3 Brad Pitt

  • ..............



2. Flyers (id, distributor_id, person_who_received_the_flyer, (datetime)datereceived, (int)received, (int)special)

  • 1 1 "John" 01/01/2009 1 1

  • 2 1 "Michael" 02/01/2009 1 0

  • 3 1 "Caine" 02/01/2009 1 1

  • 4 1 "Loris" 03/01/2009 1 1

  • 5 2 "Carpathian" 03/01/2009 0 0

  • ...........



Flyers table already has a number of potential clients.
I need to get the total number of flyers for each distribuitor, the total number of flyers given for each distribuitor and the number of deliveries marked as special for every distinct date.

something like

Carrie Bradshah

  • 01/01/2009 total:1, given 1, special 1

  • 02/01/2009 total:2, given 2, special 1

  • 03/01/2009 total:1, given 1, special 1



AnnaLynne McCord

  • 03/01/2009 total:1, given:0, special 0



This is what I have but it does not work.


SELECT DISTINCT convert(datetime,floor(convert(float, flyers.datereceived))) as datereceived,
       distribuitors.id, 
       distribuitors.name, 
       COUNT(flyers.id) AS total,
       SUM(CASE WHEN (dbo.flyers.received = 1) THEN 1 ELSE 0 END) AS received, 
       SUM(CASE WHEN (dbo.flyers.received = 1 AND dbo.flyers.special = 1)) THEN 1 ELSE 0 END) AS specials, 
       FROM distribuitors
       INNER JOIN flyers
                    	ON distribuitors.id = flyers.distribuitor_id
			
      GROUP BY flyers.datereceived, 
                       distribuitors.id, 
                       distribuitors.name 

      ORDER BY distribuitors.name



-- SQL Manager 2008 for SQL Server 3.2.0.2
-- ---------------------------------------
-- Host      : (local)
-- Database  : testorama
-- Version   : Microsoft SQL Server  9.00.1399.06


CREATE DATABASE [testorama]
ON PRIMARY
  ( NAME = [testorama],
    FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\testorama.mdf',
    SIZE = 2240 KB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1 MB )
LOG ON
  ( NAME = [testorama_log],
    FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\testorama_log.LDF',
    SIZE = 504 KB,
    MAXSIZE = 0 MB,
    FILEGROWTH = 10 % )
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

USE [testorama]
GO

--
-- Definition for contract DEFAULT : 
--

CREATE CONTRACT [DEFAULT] 
  AUTHORIZATION [dbo]
  ([DEFAULT] SENT BY ANY)
GO

--
-- Definition for table flyers : 
--

CREATE TABLE [dbo].[flyers] (
  [id] int IDENTITY(1, 1) NOT NULL,
  [distribuitor_id] int NULL,
  [person_who_received_the_flier] varchar(75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [datereceived] datetime NULL,
  [received] int NULL,
  [special] int NULL
)
ON [PRIMARY]
GO

--
-- Definition for table distributors : 
--

CREATE TABLE [dbo].[distributors] (
  [id] int IDENTITY(1, 1) NOT NULL,
  [name] varchar(75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO

--
-- Data for table dbo.distributors  (LIMIT 0,500)
--

SET IDENTITY_INSERT [dbo].[distributors] ON
GO

INSERT INTO [dbo].[distributors] ([id], [name])
VALUES 
  (1, N'Carrie Bradshoaw')
GO

INSERT INTO [dbo].[distributors] ([id], [name])
VALUES 
  (2, N'AnnaLynne McCord')
GO

INSERT INTO [dbo].[distributors] ([id], [name])
VALUES 
  (3, N'Brad Pitt')
GO

SET IDENTITY_INSERT [dbo].[distributors] OFF
GO

--
-- Data for table dbo.flyers  (LIMIT 0,500)
--

SET IDENTITY_INSERT [dbo].[flyers] ON
GO

INSERT INTO [dbo].[flyers] ([id], [distribuitor_id], [person_who_received_the_flier], [datereceived], [received], [special])
VALUES 
  (1, 1, N'John', '20090101', 1, 1)
GO

INSERT INTO [dbo].[flyers] ([id], [distribuitor_id], [person_who_received_the_flier], [datereceived], [received], [special])
VALUES 
  (2, 1, N'Michael', '20090102', 1, 0)
GO

INSERT INTO [dbo].[flyers] ([id], [distribuitor_id], [person_who_received_the_flier], [datereceived], [received], [special])
VALUES 
  (3, 1, N'Caine', '20090102', 1, 1)
GO

INSERT INTO [dbo].[flyers] ([id], [distribuitor_id], [person_who_received_the_flier], [datereceived], [received], [special])
VALUES 
  (4, 1, N'Loris', '20090103', 1, 1)
GO

INSERT INTO [dbo].[flyers] ([id], [distribuitor_id], [person_who_received_the_flier], [datereceived], [received], [special])
VALUES 
  (5, 2, N'Carpathian', '20090103', 0, 0)
GO

SET IDENTITY_INSERT [dbo].[flyers] OFF
GO

--
-- Definition for indices : 
--

ALTER TABLE [dbo].[distributors]
ADD CONSTRAINT [distributors_pk] 
PRIMARY KEY CLUSTERED ([id])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO



ALTER TABLE [dbo].[flyers]
ADD PRIMARY KEY CLUSTERED ([id])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO



--
-- Definition for foreign keys : 
--

ALTER TABLE [dbo].[flyers]
ADD CONSTRAINT [flyers_fk] FOREIGN KEY ([distribuitor_id]) 
  REFERENCES [dbo].[distributors] ([id]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

arhicoc
Starting Member

2 Posts
Posted - 2009-02-05 : 04:02:38
Got it. It was obvious, obviously.
Instead of
GROUP BY flyers.datereceived,
it was suppposed to be
GROUP BY DISTINCT convert(datetime,floor(convert(float, flyers.datereceived)))
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2009-02-07 : 20:40:58
You don't need distinct over here as you are using Group By.


quote:
Originally posted by arhicoc

Two tables


1. Distributors (id, name)

  • 1 Carrie Bradshaw

  • 2 AnnaLynne McCord

  • 3 Brad Pitt

  • ..............



2. Flyers (id, distributor_id, person_who_received_the_flyer, (datetime)datereceived, (int)received, (int)special)

  • 1 1 "John" 01/01/2009 1 1

  • 2 1 "Michael" 02/01/2009 1 0

  • 3 1 "Caine" 02/01/2009 1 1

  • 4 1 "Loris" 03/01/2009 1 1

  • 5 2 "Carpathian" 03/01/2009 0 0

  • ...........



Flyers table already has a number of potential clients.
I need to get the total number of flyers for each distribuitor, the total number of flyers given for each distribuitor and the number of deliveries marked as special for every distinct date.

something like

Carrie Bradshah

  • 01/01/2009 total:1, given 1, special 1

  • 02/01/2009 total:2, given 2, special 1

  • 03/01/2009 total:1, given 1, special 1



AnnaLynne McCord

  • 03/01/2009 total:1, given:0, special 0



This is what I have but it does not work.


SELECT DISTINCT convert(datetime,floor(convert(float, flyers.datereceived))) as datereceived,
       distribuitors.id, 
       distribuitors.name, 
       COUNT(flyers.id) AS total,
       SUM(CASE WHEN (dbo.flyers.received = 1) THEN 1 ELSE 0 END) AS received, 
       SUM(CASE WHEN (dbo.flyers.received = 1 AND dbo.flyers.special = 1)) THEN 1 ELSE 0 END) AS specials, 
       FROM distribuitors
       INNER JOIN flyers
                    	ON distribuitors.id = flyers.distribuitor_id
			
      GROUP BY convert(datetime,floor(convert(float, flyers.datereceived)), 
                       distribuitors.id, 
                       distribuitors.name 

      ORDER BY distribuitors.name



-- SQL Manager 2008 for SQL Server 3.2.0.2
-- ---------------------------------------
-- Host      : (local)
-- Database  : testorama
-- Version   : Microsoft SQL Server  9.00.1399.06


CREATE DATABASE [testorama]
ON PRIMARY
  ( NAME = [testorama],
    FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\testorama.mdf',
    SIZE = 2240 KB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1 MB )
LOG ON
  ( NAME = [testorama_log],
    FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\testorama_log.LDF',
    SIZE = 504 KB,
    MAXSIZE = 0 MB,
    FILEGROWTH = 10 % )
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

USE [testorama]
GO

--
-- Definition for contract DEFAULT : 
--

CREATE CONTRACT [DEFAULT] 
  AUTHORIZATION [dbo]
  ([DEFAULT] SENT BY ANY)
GO

--
-- Definition for table flyers : 
--

CREATE TABLE [dbo].[flyers] (
  [id] int IDENTITY(1, 1) NOT NULL,
  [distribuitor_id] int NULL,
  [person_who_received_the_flier] varchar(75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [datereceived] datetime NULL,
  [received] int NULL,
  [special] int NULL
)
ON [PRIMARY]
GO

--
-- Definition for table distributors : 
--

CREATE TABLE [dbo].[distributors] (
  [id] int IDENTITY(1, 1) NOT NULL,
  [name] varchar(75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO

--
-- Data for table dbo.distributors  (LIMIT 0,500)
--

SET IDENTITY_INSERT [dbo].[distributors] ON
GO

INSERT INTO [dbo].[distributors] ([id], [name])
VALUES 
  (1, N'Carrie Bradshoaw')
GO

INSERT INTO [dbo].[distributors] ([id], [name])
VALUES 
  (2, N'AnnaLynne McCord')
GO

INSERT INTO [dbo].[distributors] ([id], [name])
VALUES 
  (3, N'Brad Pitt')
GO

SET IDENTITY_INSERT [dbo].[distributors] OFF
GO

--
-- Data for table dbo.flyers  (LIMIT 0,500)
--

SET IDENTITY_INSERT [dbo].[flyers] ON
GO

INSERT INTO [dbo].[flyers] ([id], [distribuitor_id], [person_who_received_the_flier], [datereceived], [received], [special])
VALUES 
  (1, 1, N'John', '20090101', 1, 1)
GO

INSERT INTO [dbo].[flyers] ([id], [distribuitor_id], [person_who_received_the_flier], [datereceived], [received], [special])
VALUES 
  (2, 1, N'Michael', '20090102', 1, 0)
GO

INSERT INTO [dbo].[flyers] ([id], [distribuitor_id], [person_who_received_the_flier], [datereceived], [received], [special])
VALUES 
  (3, 1, N'Caine', '20090102', 1, 1)
GO

INSERT INTO [dbo].[flyers] ([id], [distribuitor_id], [person_who_received_the_flier], [datereceived], [received], [special])
VALUES 
  (4, 1, N'Loris', '20090103', 1, 1)
GO

INSERT INTO [dbo].[flyers] ([id], [distribuitor_id], [person_who_received_the_flier], [datereceived], [received], [special])
VALUES 
  (5, 2, N'Carpathian', '20090103', 0, 0)
GO

SET IDENTITY_INSERT [dbo].[flyers] OFF
GO

--
-- Definition for indices : 
--

ALTER TABLE [dbo].[distributors]
ADD CONSTRAINT [distributors_pk] 
PRIMARY KEY CLUSTERED ([id])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO



ALTER TABLE [dbo].[flyers]
ADD PRIMARY KEY CLUSTERED ([id])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO



--
-- Definition for foreign keys : 
--

ALTER TABLE [dbo].[flyers]
ADD CONSTRAINT [flyers_fk] FOREIGN KEY ([distribuitor_id]) 
  REFERENCES [dbo].[distributors] ([id]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site