Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

SQL SERVER Duplicated Row Data in Subquery

Tags:

sql-server

I want to extract the result of a person in a poll by type of poll and a determined year, group by area.

This is the tables scripts and some sample data:

/****** Object:  Table [dbo].[ANSWERS]    Script Date: 09/09/2015 7:08:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ANSWERS](
    [FK_QUESTION] [int] NOT NULL,
    [CHOICE] [smallint] NOT NULL,
    [TYPE_POLL] [bit] NOT NULL,
    [FK_ID_PERSON] [decimal](18, 0) NOT NULL,
 CONSTRAINT [PK_ANSWERS] PRIMARY KEY CLUSTERED 
(
    [FK_QUESTION] ASC,
    [TYPE_POLL] ASC,
    [FK_ID_PERSON] 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].[AREA]    Script Date: 09/09/2015 7:08:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AREA](
    [ID_AREA] [int] IDENTITY(1,1) NOT NULL,
    [FK_POLL] [int] NULL,
    [NAME_AREA] [varchar](100) NULL,
 CONSTRAINT [PK_AREA] PRIMARY KEY CLUSTERED 
(
    [ID_AREA] 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].[JOBS]    Script Date: 09/09/2015 7:08:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[JOBS](
    [ID_JOB] [int] NOT NULL,
    [NAME_JOB] [varchar](50) NOT NULL,
 CONSTRAINT [PK_srh3100t] PRIMARY KEY CLUSTERED 
(
    [ID_JOB] 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].[PERSON]    Script Date: 09/09/2015 7:08:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PERSON](
    [ID_PERSON] [decimal](18, 0) NOT NULL,
    [JOB_PERSON] [int] NOT NULL,
    [NAME] [varchar](200) NOT NULL,
    [LAST_NAME] [varchar](200) NOT NULL,
 CONSTRAINT [PK_PERSON] PRIMARY KEY CLUSTERED 
(
    [ID_PERSON] 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].[POINTS]    Script Date: 09/09/2015 7:08:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[POINTS](
    [FK_JOB] [int] NOT NULL,
    [FK_QUESTION] [int] NOT NULL,
    [POINT] [numeric](4, 2) NOT NULL,
 CONSTRAINT [PK_POINTS_1] PRIMARY KEY CLUSTERED 
(
    [FK_JOB] ASC,
    [FK_QUESTION] 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].[POLL]    Script Date: 09/09/2015 7:08:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[POLL](
    [ID_POLL] [int] IDENTITY(1,1) NOT NULL,
    [YEARS] [smallint] NOT NULL,
    [NAME] [varchar](100) NOT NULL,
    [DATE_BEG] [date] NOT NULL,
    [DATE_END] [date] NOT NULL,
    [AUTO] [numeric](4, 2) NOT NULL,
    [BOSS] [numeric](4, 2) NOT NULL,
 CONSTRAINT [PK_POLL_1] PRIMARY KEY CLUSTERED 
(
    [ID_POLL] 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].[QUESTION]    Script Date: 09/09/2015 7:08:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[QUESTION](
    [ID_QUESTION] [int] IDENTITY(1,1) NOT NULL,
    [FK_AREA] [int] NULL,
    [NAME] [varchar](50) NULL,
    [DESCRIPTION] [varchar](400) NULL,
 CONSTRAINT [PK_QUESTION] PRIMARY KEY CLUSTERED 
(
    [ID_QUESTION] 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
INSERT [dbo].[ANSWERS] ([FK_QUESTION], [CHOICE], [TYPE_POLL], [FK_ID_PERSON]) VALUES (1, 1, 0, CAST(1 AS Decimal(18, 0)))
INSERT [dbo].[ANSWERS] ([FK_QUESTION], [CHOICE], [TYPE_POLL], [FK_ID_PERSON]) VALUES (1, 1, 1, CAST(1 AS Decimal(18, 0)))
INSERT [dbo].[ANSWERS] ([FK_QUESTION], [CHOICE], [TYPE_POLL], [FK_ID_PERSON]) VALUES (2, 1, 0, CAST(1 AS Decimal(18, 0)))
INSERT [dbo].[ANSWERS] ([FK_QUESTION], [CHOICE], [TYPE_POLL], [FK_ID_PERSON]) VALUES (2, 2, 1, CAST(1 AS Decimal(18, 0)))
INSERT [dbo].[ANSWERS] ([FK_QUESTION], [CHOICE], [TYPE_POLL], [FK_ID_PERSON]) VALUES (3, 3, 0, CAST(1 AS Decimal(18, 0)))
INSERT [dbo].[ANSWERS] ([FK_QUESTION], [CHOICE], [TYPE_POLL], [FK_ID_PERSON]) VALUES (3, 1, 1, CAST(1 AS Decimal(18, 0)))
SET IDENTITY_INSERT [dbo].[AREA] ON 

INSERT [dbo].[AREA] ([ID_AREA], [FK_POLL], [NAME_AREA]) VALUES (1, 1, N'AREA 1')
INSERT [dbo].[AREA] ([ID_AREA], [FK_POLL], [NAME_AREA]) VALUES (2, 1, N'AREA 2')
INSERT [dbo].[AREA] ([ID_AREA], [FK_POLL], [NAME_AREA]) VALUES (3, 1, N'AREA 3')
INSERT [dbo].[AREA] ([ID_AREA], [FK_POLL], [NAME_AREA]) VALUES (4, 1, N'AREA 4')
SET IDENTITY_INSERT [dbo].[AREA] OFF
INSERT [dbo].[JOBS] ([ID_JOB], [NAME_JOB]) VALUES (1, N'JOB 1')
INSERT [dbo].[JOBS] ([ID_JOB], [NAME_JOB]) VALUES (2, N'JOB 2')
INSERT [dbo].[JOBS] ([ID_JOB], [NAME_JOB]) VALUES (3, N'JOB  3')
INSERT [dbo].[JOBS] ([ID_JOB], [NAME_JOB]) VALUES (4, N'JOB 4')
INSERT [dbo].[PERSON] ([ID_PERSON], [JOB_PERSON], [NAME], [LAST_NAME]) VALUES (CAST(1 AS Decimal(18, 0)), 1, N'MATT', N'TUCK')
INSERT [dbo].[PERSON] ([ID_PERSON], [JOB_PERSON], [NAME], [LAST_NAME]) VALUES (CAST(2 AS Decimal(18, 0)), 2, N'MATT', N'HEAFY')
INSERT [dbo].[PERSON] ([ID_PERSON], [JOB_PERSON], [NAME], [LAST_NAME]) VALUES (CAST(3 AS Decimal(18, 0)), 3, N'SYNISTER', N'GATES')
INSERT [dbo].[PERSON] ([ID_PERSON], [JOB_PERSON], [NAME], [LAST_NAME]) VALUES (CAST(4 AS Decimal(18, 0)), 4, N'THOMAS', N'YOUNGBLOOD')
INSERT [dbo].[POINTS] ([FK_JOB], [FK_QUESTION], [POINT]) VALUES (1, 1, CAST(1.00 AS Numeric(4, 2)))
INSERT [dbo].[POINTS] ([FK_JOB], [FK_QUESTION], [POINT]) VALUES (1, 2, CAST(2.00 AS Numeric(4, 2)))
INSERT [dbo].[POINTS] ([FK_JOB], [FK_QUESTION], [POINT]) VALUES (1, 3, CAST(3.00 AS Numeric(4, 2)))
INSERT [dbo].[POINTS] ([FK_JOB], [FK_QUESTION], [POINT]) VALUES (1, 4, CAST(4.00 AS Numeric(4, 2)))
INSERT [dbo].[POINTS] ([FK_JOB], [FK_QUESTION], [POINT]) VALUES (2, 1, CAST(1.00 AS Numeric(4, 2)))
INSERT [dbo].[POINTS] ([FK_JOB], [FK_QUESTION], [POINT]) VALUES (2, 2, CAST(2.00 AS Numeric(4, 2)))
INSERT [dbo].[POINTS] ([FK_JOB], [FK_QUESTION], [POINT]) VALUES (2, 3, CAST(3.00 AS Numeric(4, 2)))
INSERT [dbo].[POINTS] ([FK_JOB], [FK_QUESTION], [POINT]) VALUES (2, 4, CAST(4.00 AS Numeric(4, 2)))
SET IDENTITY_INSERT [dbo].[POLL] ON 

INSERT [dbo].[POLL] ([ID_POLL], [YEARS], [NAME], [DATE_BEG], [DATE_END], [AUTO], [BOSS]) VALUES (1, 2015, N'POLL 1', CAST(0x693A0B00 AS Date), CAST(0xD23A0B00 AS Date), CAST(20.00 AS Numeric(4, 2)), CAST(80.00 AS Numeric(4, 2)))
SET IDENTITY_INSERT [dbo].[POLL] OFF
SET IDENTITY_INSERT [dbo].[QUESTION] ON 

INSERT [dbo].[QUESTION] ([ID_QUESTION], [FK_AREA], [NAME], [DESCRIPTION]) VALUES (1, 1, N'QUESTION 1', N'aaa')
INSERT [dbo].[QUESTION] ([ID_QUESTION], [FK_AREA], [NAME], [DESCRIPTION]) VALUES (2, 1, N'QUESTION 2', N'bbb')
INSERT [dbo].[QUESTION] ([ID_QUESTION], [FK_AREA], [NAME], [DESCRIPTION]) VALUES (3, 2, N'QUESTION 3', N'ccc')
INSERT [dbo].[QUESTION] ([ID_QUESTION], [FK_AREA], [NAME], [DESCRIPTION]) VALUES (4, 2, N'QUESTION 4', N'ddd')
INSERT [dbo].[QUESTION] ([ID_QUESTION], [FK_AREA], [NAME], [DESCRIPTION]) VALUES (5, 3, N'QUESTION 5', N'eee')
INSERT [dbo].[QUESTION] ([ID_QUESTION], [FK_AREA], [NAME], [DESCRIPTION]) VALUES (6, 3, N'QUESTION 6', N'fff')
INSERT [dbo].[QUESTION] ([ID_QUESTION], [FK_AREA], [NAME], [DESCRIPTION]) VALUES (7, 4, N'QUESTION 7', N'ggg')
INSERT [dbo].[QUESTION] ([ID_QUESTION], [FK_AREA], [NAME], [DESCRIPTION]) VALUES (8, 4, N'QUESTION 8', N'hhh')
SET IDENTITY_INSERT [dbo].[QUESTION] OFF
USE [master]
GO

And this is the query I have tried:

SELECT AREA.NAME_AREA,
  (SELECT SUM(ANSWERS.CHOICE * POINTS.POINT / 5) WHERE ANSWERS.TYPE_POLL= 0) AS RESULT_PERSON,
  (SELECT SUM(ANSWERS.CHOICE * POINTS.POINT / 5) WHERE ANSWERS.TYPE_POLL= 1) AS RESULT_BOSS
  FROM  POLL INNER JOIN
                         AREA ON POLL.ID_POLL = AREA.FK_POLL INNER JOIN
                         QUESTION ON AREA.ID_AREA = QUESTION.FK_AREA INNER JOIN
                         POINTS ON QUESTION.ID_QUESTION = POINTS.FK_QUESTION INNER JOIN
                         JOBS INNER JOIN
                         PERSON ON JOBS.ID_JOB = PERSON.JOB_PERSON ON POINTS.FK_JOB = JOBS.ID_JOB INNER JOIN
                         ANSWERS ON QUESTION.ID_QUESTION = ANSWERS.FK_QUESTION AND PERSON.ID_PERSON = ANSWERS.FK_ID_PERSON
WHERE        (POLL.YEARS = 2015) AND (PERSON.ID_PERSON = 1) GROUP BY AREA.NAME_AREA , ANSWERS.TYPE_POLL

And I want the output be like this:

+------------+---------------+---------------+
| NAME_AREA  | RESULT_PERSON | RESULT_BOSS   |
+------------+---------------+---------------+
| AREA 1     |           0.6 |     1         |
| AREA 2     |           1.8 |     0.6       |
+------------+---------------+---------------+

But I'm getting the result like this:

+------------+---------------+---------------+
| NAME_AREA  | RESULT_PERSON | RESULT_BOSS   |
+------------+---------------+---------------+
| AREA 1     |           0.6 |     NULL      |
| AREA 2     |           1.8 |     NULL      |
| AREA 1     |        NULL   |             1 |
| AREA 2     |        NULL   |           0.6 |
+------------+---------------+---------------+

It is something I am missing...?

like image 420
CyborgNinja23 Avatar asked Jul 12 '26 15:07

CyborgNinja23

1 Answers

Without reading your query and finding better solution: 

SELECT  t.NAME_AREA, MAX(t.RESULT_PERSON) AS RESULT_PERSON, MAX(t.RESULT_BOSS) AS RESULT_BOSS
FROM (
SELECT AREA.NAME_AREA,
  (SELECT SUM(ANSWERS.CHOICE * POINTS.POINT / 5) WHERE ANSWERS.TYPE_POLL= 0) AS RESULT_PERSON,
  (SELECT SUM(ANSWERS.CHOICE * POINTS.POINT / 5) WHERE ANSWERS.TYPE_POLL= 1) AS RESULT_BOSS
  FROM  POLL INNER JOIN
                         AREA ON POLL.ID_POLL = AREA.FK_POLL INNER JOIN
                         QUESTION ON AREA.ID_AREA = QUESTION.FK_AREA INNER JOIN
                         POINTS ON QUESTION.ID_QUESTION = POINTS.FK_QUESTION INNER JOIN
                         JOBS INNER JOIN
                         PERSON ON JOBS.ID_JOB = PERSON.JOB_PERSON ON POINTS.FK_JOB = JOBS.ID_JOB INNER JOIN
                         ANSWERS ON QUESTION.ID_QUESTION = ANSWERS.FK_QUESTION AND PERSON.ID_PERSON = ANSWERS.FK_ID_PERSON
WHERE        (POLL.YEARS = 2015) AND (PERSON.ID_PERSON = 1) GROUP BY AREA.NAME_AREA , ANSWERS.TYPE_POLL)
AS t
GROUP BY NAME_AREA;

I am aware there are more efficient and secure ways, this is just quick workaround.

like image 131
Lukasz Szozda Avatar answered Jul 15 '26 06:07

Lukasz Szozda
Sign in to Comment

Related questions

Using EXISTS to find rows with Maximum value

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!