Link to home
Start Free TrialLog in
Avatar of Chris Jones
Chris JonesFlag for United States of America

asked on

SQL Help

Hello,

I have two tables one with fields I need such as ID, clientID, table  andCustomerID the second table has the ID of the record in table and a total column. I am trying to get a subtotal based on the ID and the client ID. here is my SQL that may help....
SELECT A.ID, A.CustomerID, A.ClientID, CONVERT(varchar(20),SUM(B.Total) 
FROM     SureSign.SUR_Data AS A INNER JOIN
                  SUR_OCR_Data AS B ON A.ID = B.InvoiceID
WHERE  (A.ClientID = 'defjam903')
GROUP BY A.CustomerID
ORDER BY A.CustomerID

Open in new window




Table 1
CREATE TABLE [dbo].[SUR_OCR_Data](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[InvoiceID] [varchar](max) NULL,
	[Total] [varchar](50) NULL,
	[Subtotal] [varchar](50) NULL,
	[DateAdd] [datetime] NULL CONSTRAINT [DF_SUR_OCR_Data_DateAdd]  DEFAULT (getdate())
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Open in new window




Table2
CREATE TABLE [SureSign].[SUR_Data](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CustomerID] [varchar](max) NULL,
	[ClientID] [varchar](max) NULL,
	[InvoiceID] [varchar](max) NULL,
	[Name] [varchar](max) NULL,
	[ContentType] [varchar](max) NULL,
	[Data] [varbinary](max) NULL,
	[Status] [varchar](max) NULL,
	[Comments] [varchar](max) NULL,
	[DateApprove] [varchar](max) NULL,
	[DateAdd] [datetime] NULL CONSTRAINT [DF_SUR_Data_DateAdd]  DEFAULT (getdate()),
	[Process] [varchar](50) NULL CONSTRAINT [DF_SUR_Data_Process]  DEFAULT ('N'),
	[Template] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Open in new window

Avatar of EugeneZ
EugeneZ
Flag of United States of America image

did you get an error?
-- check this


SELECT A.ID, A.CustomerID, A.ClientID, SUM(cast (B.Total as int) )
FROM     SureSign.SUR_Data AS A INNER JOIN
                  SUR_OCR_Data AS B ON A.ID = B.InvoiceID
WHERE  (A.ClientID = 'defjam903') and ISNUMERIC(B.Total)=1
GROUP BY A.ID, A.CustomerID, A.ClientID
ORDER BY A.CustomerID
CONVERT(varchar(20),SUM(B.Total))

missing ) at the end
Mike:

[Total] is [varchar](50)  and you may have problems to SUM on varchar (e.g get error "Operand data type varchar is invalid for sum operator.")

However, just as an idea to be close to the request

SELECT A.ID, A.CustomerID, A.ClientID, cast (SUM(cast (B.Total as int) ) as varchar(50))
FROM     dbo.SUR_Data AS A INNER JOIN
                  SUR_OCR_Data AS B ON A.ID = B.InvoiceID
WHERE  (A.ClientID = 'defjam903') and ISNUMERIC(B.Total)=1
GROUP BY A.ID, A.CustomerID, A.ClientID
ORDER BY A.CustomerID

Open in new window

As an aside:

An ID column is varchar(max)??  This could have been done only by consultants/contractors, right?!
Avatar of Chris Jones

ASKER

yes @scott i am working on changing it  and a few other columns but needed to get this working[/indent]
here is the error that I received but the field is covered so I aam not sure where the error is coming from.

User generated image
You can't have decimal points in an "int" value.  Cast it to a decimal value instead:

SELECT CAST(SUM(CAST(B.Total AS decimal(19, 4)) ...
ok this is what I have I think I have been looking at the code too long today

SELECT A.ID, A.CustomerID, A.ClientID, CAST(SUM(CAST(B.Total AS decimal(19, 4)) AS varchar(50)) AS Expr1
FROM     SureSign.SUR_Data AS A INNER JOIN
                  SUR_OCR_Data AS B ON A.ID = B.InvoiceID
WHERE  (ISNUMERIC(B.Total) = 1) AND (A.ClientID = 'defjam903')
GROUP BY A.ID, A.CustomerID, A.ClientID
ORDER BY A.CustomerID

Open in new window



User generated image
i think :EugeneZ is right the field can't be Varchar
sorry i posted that wrong

SELECT A.ID, A.CustomerID, A.ClientID, CAST(SUM(CAST(B.Total AS decimal(19, 4)) AS Expr1
FROM     SureSign.SUR_Data AS A INNER JOIN
                  SUR_OCR_Data AS B ON A.ID = B.InvoiceID
WHERE  (ISNUMERIC(B.Total) = 1) AND (A.ClientID = 'defjam903')



User generated imageGROUP BY A.ID, A.CustomerID, A.ClientID
ORDER BY A.CustomerID
In that code, you have 4 open parens in the last expression but only 2 close parens -- that ain't gonna work :-).
try again

this one
SELECT A.ID, A.CustomerID, A.ClientID, cast (SUM(cast (B.Total as dec(18,4)) ) as varchar(50)) STotal
FROM     dbo.SUR_Data AS A INNER JOIN
                  SUR_OCR_Data AS B ON A.ID = B.InvoiceID
WHERE  (A.ClientID = 'defjam903') and ISNUMERIC(B.Total)=1
GROUP BY A.ID, A.CustomerID, A.ClientID
ORDER BY A.CustomerID

Open in new window

@EugeneZ new error with conversion

User generated image
hmm -- see if you can fix data type

try this one:



SELECT A.ID, A.CustomerID, A.ClientID, cast (SUM(cast (B.Total as dec(18,4)) ) as varchar(50)) STotal
FROM     dbo.SUR_Data AS A INNER JOIN
                  SUR_OCR_Data AS B ON A.ID = B.InvoiceID
WHERE  (A.ClientID = 'defjam903') and ISNUMERIC(B.Total+ '.0e0')=1 AND B.Total IS NOT NULL
GROUP BY A.ID, A.CustomerID, A.ClientID
ORDER BY A.CustomerID

Open in new window

Are you on SQL 2012 or later?  Or SQL 2008 R2 or earlier?
yes i am on SQL 2012
ok this worked but returned no values

SELECT A.ID, A.CustomerID, A.ClientID, CAST(SUM(CAST(B.Total AS dec(18, 4))) AS varchar(50)) AS STotal
FROM     SureSign.SUR_Data AS A INNER JOIN
                  SUR_OCR_Data AS B ON A.ID = B.InvoiceID
WHERE  (A.ClientID = 'defjam903') AND (ISNUMERIC(B.Total + '.0e0') = 1) AND (B.Total IS NOT NULL)
GROUP BY A.ID, A.CustomerID, A.ClientID
ORDER BY A.CustomerID

Open in new window

also just to not the above SQL seems to work but if the column has a, in it fails on that record
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Still the same error @Scott
please check data stored in Total -- you need to clean it - and set Numeric datatype

try this  one ?

SELECT A.ID, A.CustomerID, A.ClientID, CAST(SUM(CAST(B.Total AS dec(18, 4))) AS varchar(50)) AS STotal
FROM     SureSign.SUR_Data AS A INNER JOIN
                  SUR_OCR_Data AS B ON A.ID = B.InvoiceID
WHERE  (A.ClientID = 'defjam903') AND (ISNUMERIC(B.Total) = 1) AND (B.Total IS NOT NULL)
GROUP BY A.ID, A.CustomerID, A.ClientID
ORDER BY A.CustomerID
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Author,
Can you send out the data. So can write the query with testing.

Thank u.
Sure


Table A
28	cjones12@leomail.tamuc.edu	defjam903	NULL	SureSign.pdf	application/pdf	<Binary data>	F	NULL	2017/01/20 22:22:34	2017-01-20 20:22:45.760	Y	NULL
50	cjones12@leomail.tamuc.edu	defjam903	SUR2017325	SKMBT_C36417013011270.pdf	application/pdf	<Binary data>	N	NULL	NULL	2017-02-03 07:39:38.533	N	SureSignTest
30	defjam903	lacybee	NULL	SureSign.pdf	application/pdf	<Binary data>	F	NULL	2017/01/20 22:27:32	2017-01-20 20:27:44.243	N	NULL
36	cjones12@leomail.tamuc.edu	lacybee	NULL	Document (2).pdf	application/pdf	<Binary data>	F	NULL	2017/01/21 08:15:01	2017-01-21 06:15:11.973	N	NULL
42	cjones12@leomail.tamuc.edu	defjam903	NULL	2017 Annual Meeting Notice (1).pdf	application/pdf	<Binary data>	F	NULL	2017/01/21 08:29:02	2017-01-21 06:28:56.493	N	NULL
48	cjones12@leomail.tamuc.edu	lacybee	NULL	SKMBT_C36417012517120.pdf	application/pdf	<Binary data>	F	NULL	2017/02/02 10:19:07	2017-02-02 08:19:11.890	N	NULL
49	lacybee	defjam903	NULL	SKMBT_C36417013011270 (1).pdf	application/pdf	<Binary data>	F	NULL	2017/02/02 10:19:17	2017-02-02 08:19:23.203	N	NULL
40	cjones12@leomail.tamuc.edu	defjam903	NULL	WriteCPM.pdf	application/pdf	<Binary data>	F	NULL	2017/01/21 08:23:37	2017-01-21 06:23:47.850	N	NULL
38	lacybee	defjam903	NULL	Dell - Cart.pdf	application/pdf	<Binary data>	F	NULL	2017/01/21 08:20:58	2017-01-21 06:21:09.413	N	NULL
32	cjones12@leomail.tamuc.edu	defjam903	NULL	SureSign.pdf	application/pdf	<Binary data>	F	NULL	2017/01/21 08:06:32	2017-01-21 06:06:42.840	N	NULL
43	lacybee	defjam903	NULL	2017 Annual Meeting Notice (1).pdf	application/pdf	<Binary data>	F	NULL	2017/01/21 08:29:19	2017-01-21 06:29:30.060	N	NULL
45	cjones12@leomail.tamuc.edu	defjam903	NULL	TAB.pdf	application/pdf	<Binary data>	F	NULL	2017/01/21 08:39:07	2017-01-21 06:39:01.917	N	NULL
34	cjones12@leomail.tamuc.edu	defjam903	NULL	Document (2).pdf	application/pdf	<Binary data>	F	NULL	2017/01/21 08:10:47	2017-01-21 06:10:57.923	N	NULL
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

Open in new window


Table B
8	49	2,076.6	NULL	2017-02-02 10:31:22.497
9	30	2,076.6	NULL	2017-02-02 10:31:22.497
10	36	1,076.6	NULL	2017-02-02 10:31:22.497
11	45	900.0	NULL	2017-02-02 10:31:22.497
NULL	NULL	NULL	NULL	NULL

Open in new window

Hi Chris,
Please find the tested and working solution below. This shall work in any SQL Server Version

Note - Your column data types are on very higher side. Please change them. You dont need MAX in all the columns.

SELECT Id,CustomerID,ClientID,SUM(total) total
FROM
(
	SELECT A.ID, A.CustomerID, A.ClientID, CAST(REPLACE(B.Total,',','') AS NUMERIC(20,4)) total
	FROM    SUR_Data AS A INNER JOIN
					  SUR_OCR_Data AS B ON A.ID = B.InvoiceID
	WHERE  (A.ClientID = 'defjam903') and CAST(REPLACE(B.Total,',','') AS NUMERIC(20,4)) is not null 
)K GROUP BY Id,CustomerID,ClientID

Open in new window


Output

/*------------------------
OUTPUT
------------------------*/
Id          CustomerID                                                                                                                                                                                                                                                       ClientID                                                                                                                                                                                                                                                         total
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------
45          cjones12@leomail.tamuc.edu                                                                                                                                                                                                                                       defjam903                                                                                                                                                                                                                                                        900.0000
49          lacybee                                                                                                                                                                                                                                                          defjam903                                                                                                                                                                                                                                                        2076.6000

(2 row(s) affected)

Open in new window


Hope it helps!
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial