Query with multiple conditions (SQL 2016)

Question

Query with multiple conditions (SQL 2016)

ShawnBryan

SSCrazy

Points: 2157
More actions
January 25, 2017 at 1:55 pm

#325970

Experts i am getting error while i execute the query ,
error " Msg 144, Level 15, State 1, Line 17"
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.
SELECT [sddoc], [Soldtopt], [tradingname], [DlvDate], SUM(try_cast(Netvalue as float)) as Netvalue, count(distinct SDDoc) as Salesdoc , count(distinct case when Netvalue = '0' then 1 else null end) as ZeroValue , count(distinct SDDoc) - count(distinct case when Netvalue = '0' then 1 else null end) As Result
FROM [FOC].[dbo].[foc]
WHERE dlvdate='25.01.2017'
GROUP by case when SUM(try_cast(Netvalue as float)) = 0 and count(distinct SDDoc) = 1 and count(distinct case when Netvalue = '0' then 1 else null end) = 1 then [sddoc] end, Soldtopt,tradingname,DlvDate
expected result: EXAMPLE.
MY DML. DDL USE [FOC]
GO
/****** Object: Table [dbo].[foc] Script Date: 1/26/2017 12:02:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[foc](
[SDDoc] [varchar](50) NULL,
[Soldtopt] [varchar](50) NULL,
[TradingName] [varchar](50) NULL,
[DocDate] [varchar](50) NULL,
[DlvDate] [varchar](50) NULL,
[Material] [varchar](50) NULL,
[Description] [varchar](50) NULL,
[Orderqty] [varchar](50) NULL,
[ConfirmQty] [varchar](50) NULL,
[Item] [varchar](50) NULL,
[Netprice] [varchar](50) NULL,
[Netvalue] [varchar](50) NULL
) ON [PRIMARY]
USE [FOC] GO INSERT [dbo].[foc] ([SDDoc], [Soldtopt], [TradingName], [DocDate], [DlvDate], [Material], [Description], [Orderqty], [ConfirmQty], [Item], [Netprice], [Netvalue]) VALUES (N'3676629', N'9008519', N'ALHIDD COLD STORE', N'23.01.2016', N'23.01.2016', N'10231700', N'Co', N'2', N'2', N'10', N'0', N'0') GO INSERT [dbo].[foc] ([SDDoc], [Soldtopt], [TradingName], [DocDate], [DlvDate], [Material], [Description], [Orderqty], [ConfirmQty], [Item], [Netprice], [Netvalue]) VALUES (N'3676629', N'9008519', N'ALHIDD COLD STORE', N'23.01.2016', N'23.01.2016', N'10611700', N'C', N'2', N'2', N'20', N'3.3', N'45.3') GO INSERT [dbo].[foc] ([SDDoc], [Soldtopt], [TradingName], [DocDate], [DlvDate], [Material], [Description], [Orderqty], [ConfirmQty], [Item], [Netprice], [Netvalue]) VALUES (N'3676629', N'9008519', N'ALHIDD COLD STORE', N'24.01.2016', N'24.01.2016', N'10761200', N'CoD', N'1', N'1', N'30', N'0', N'0') GO INSERT [dbo].[foc] ([SDDoc], [Soldtopt], [TradingName], [DocDate], [DlvDate], [Material], [Description], [Orderqty], [ConfirmQty], [Item], [Netprice], [Netvalue]) VALUES (N'3676629', N'9008519', N'STORE', N'25.01.2016', N'24.01.2016', N'11231700', N'OK', N'1', N'1', N'40', N'0', N'0') GO INSERT [dbo].[foc] ([SDDoc], [Soldtopt], [TradingName], [DocDate], [DlvDate], [Material], [Description], [Orderqty], [ConfirmQty], [Item], [Netprice], [Netvalue]) VALUES (N'3676629', N'9008519', N'STORE', N'24.01.2016', N'24.01.2016', N'11231701', N'OK1', N'1', N'1', N'40', N'10', N'10')

Viewing 9 posts - 1 through 8 (of 8 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Seems pretty clear:
You cannot use an aggregate in an expression used for the group by list of a GROUP BY clause.
Maybe you're trying to get the functionality of HAVING
SELECT [sddoc], [Soldtopt], [tradingname], [DlvDate], SUM(try_cast(Netvalue as float)) as Netvalue, count(distinct SDDoc) as Salesdoc , count(distinct case when Netvalue = '0' then 1 else null end) as ZeroValue , count(distinct SDDoc) - count(distinct case when Netvalue = '0' then 1 else null end) As Result FROM [FOC].[dbo].[foc] WHERE dlvdate='25.01.2017' GROUP by Soldtopt,tradingname,DlvDate,[sddoc]

HAVING SUM(try_cast(Netvalue as float)) = 0 and count(distinct SDDoc) = 1 and count(distinct case when Netvalue = '0' then 1 else null end) = 1;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ShawnBryan SSCrazy Points: 2157 More actions · Answer 2

Luis Cazares - Wednesday, January 25, 2017 2:10 PM
Seems pretty clear:
You cannot use an aggregate in an expression used for the group by list of a GROUP BY clause.
Maybe you're trying to get the functionality of HAVING
SELECT [sddoc], [Soldtopt], [tradingname], [DlvDate], SUM(try_cast(Netvalue as float)) as Netvalue, count(distinct SDDoc) as Salesdoc , count(distinct case when Netvalue = '0' then 1 else null end) as ZeroValue , count(distinct SDDoc) - count(distinct case when Netvalue = '0' then 1 else null end) As Result FROM [FOC].[dbo].[foc] WHERE dlvdate='25.01.2017' GROUP by Soldtopt,tradingname,DlvDate,[sddoc]
HAVING SUM(try_cast(Netvalue as float)) = 0 and count(distinct SDDoc) = 1 and count(distinct case when Netvalue = '0' then 1 else null end) = 1;

hello louis. thanks but its did work . example my dml is
GO
INSERT [dbo].[foc] ([SDDoc], [Soldtopt], [TradingName], [DocDate], [DlvDate], [Material], [Description], [Orderqty], [ConfirmQty], [Item], [Netprice], [Netvalue]) VALUES (N'3676623', N'9008521', N'ALHIDD COLD STORE', N'23.01.2016', N'29.01.2016', N'10231700', N'Co', N'2', N'2', N'10', N'0', N'0')
GO
INSERT [dbo].[foc] ([SDDoc], [Soldtopt], [TradingName], [DocDate], [DlvDate], [Material], [Description], [Orderqty], [ConfirmQty], [Item], [Netprice], [Netvalue]) VALUES (N'3676640', N'9008521', N'ALHIDD COLD STORE', N'23.01.2016', N'29.01.2016', N'10611700', N'C', N'2', N'2', N'20', N'3.3', N'45.3')
GO
INSERT [dbo].[foc] ([SDDoc], [Soldtopt], [TradingName], [DocDate], [DlvDate], [Material], [Description], [Orderqty], [ConfirmQty], [Item], [Netprice], [Netvalue]) VALUES (N'36763632', N'9008522', N'ALHIDD COLD STORE', N'24.01.2016', N'29.01.2016', N'10761200', N'CoD', N'1', N'1', N'30', N'0', N'0')
GO
INSERT [dbo].[foc] ([SDDoc], [Soldtopt], [TradingName], [DocDate], [DlvDate], [Material], [Description], [Orderqty], [ConfirmQty], [Item], [Netprice], [Netvalue]) VALUES (N'36736644', N'3008529', N'STORE', N'25.01.2016', N'29.01.2016', N'11231700', N'OK', N'1', N'1', N'40', N'10', N'10')
GO
INSERT [dbo].[foc] ([SDDoc], [Soldtopt], [TradingName], [DocDate], [DlvDate], [Material], [Description], [Orderqty], [ConfirmQty], [Item], [Netprice], [Netvalue]) VALUES (N'3676689', N'3008529', N'STORE', N'24.01.2016', N'29.01.2016', N'11231701', N'OK1', N'1', N'1', N'40', N'0', N'0')

][/code]

current error output is as per your query.

Expected ( Criteria 1, all sales doc for soldtopt netvalue should be 0 by summing all sddoc for that day for each soldtopt.. Criteria 2. as per counting salesdoc should be 1 and if its 2 sales doc then thats is not entitled for query. Criteria 3, result should be 0.
Expected result ,

drew.allen SSC Guru Points: 76988 More actions · Answer 3

When posting sample data and expected results, they must MATCH. Your sample data contains two trade names: 'Alhidd Cold Store' and 'Store', and your expected results contain five names which cannot possibly be derived from those two names. How do you expect us to arrive at values for 'Spacetoon' or 'Raqi Sweets' from 'Alhidd Cold Store' and 'Store'?

The reason we ask for sample data and expected results is so that people can TEST their code to see how close they are getting. If there is no possible path from your sample data to your expected results, it's impossible for people to tell if their code is correct.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ShawnBryan SSCrazy Points: 2157 More actions · Answer 4

drew.allen - Wednesday, January 25, 2017 3:47 PM
When posting sample data and expected results, they must MATCH. Your sample data contains two trade names: 'Alhidd Cold Store' and 'Store', and your expected results contain five names which cannot possibly be derived from those two names. How do you expect us to arrive at values for 'Spacetoon' or 'Raqi Sweets' from 'Alhidd Cold Store' and 'Store'?
The reason we ask for sample data and expected results is so that people can TEST their code to see how close they are getting. If there is no possible path from your sample data to your expected results, it's impossible for people to tell if their code is correct.
Drew

hi can you check now ?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

You need to understand what you're doing with your GROUP BY clause. If you don't have the correct columns or expressions, you'll never arrive to the expected results.
SELECT MIN([sddoc]) AS [sddoc], [Soldtopt], [tradingname], [DlvDate], SUM(try_cast(Netvalue as float)) as Netvalue, count(distinct SDDoc) as Salesdoc , count(distinct case when Netvalue = '0' then 1 else null end) as ZeroValue , count(distinct SDDoc) - count(distinct case when Netvalue = '0' then 1 else null end) As Result FROM [dbo].[foc] --WHERE dlvdate='25.01.2017' GROUP by Soldtopt,tradingname,DlvDate HAVING SUM(try_cast(Netvalue as float)) = 0 and count(distinct SDDoc) = 1 and count(distinct case when Netvalue = '0' then 1 else null end) = 1;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ShawnBryan SSCrazy Points: 2157 More actions · Answer 6

ShawnBryan

SSCrazy

Points: 2157

January 26, 2017 at 6:59 am

#1925047

Luis Thanks It worked..

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

ShawnBryan - Thursday, January 26, 2017 6:59 AM
Luis Thanks It worked..

Great!
Do you understand why/how it worked?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ShawnBryan SSCrazy Points: 2157 More actions · Answer 8

Luis Cazares - Thursday, January 26, 2017 7:02 AM
ShawnBryan - Thursday, January 26, 2017 6:59 AM
Luis Thanks It worked..
Great!
Do you understand why/how it worked?

Yes i suspect this where missing clause MIN([sddoc]) AS [sddoc], and about group clause i have tried same as your suggested where it got failed which i did not pasted earlier.