Chris Jones
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....
Table 1
Table2
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
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]
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]
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
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
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
[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
As an aside:
An ID column is varchar(max)?? This could have been done only by consultants/contractors, right?!
An ID column is varchar(max)?? This could have been done only by consultants/contractors, right?!
ASKER
yes @scott i am working on changing it and a few other columns but needed to get this working[/indent]
ASKER
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)) ...
SELECT CAST(SUM(CAST(B.Total AS decimal(19, 4)) ...
ASKER
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
ASKER
i think :EugeneZ is right the field can't be Varchar
ASKER
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')
GROUP BY A.ID, A.CustomerID, A.ClientID
ORDER BY A.CustomerID
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')
GROUP 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
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
hmm -- see if you can fix data type
try this one:
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
Are you on SQL 2012 or later? Or SQL 2008 R2 or earlier?
ASKER
yes i am on SQL 2012
ASKER
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
ASKER
also just to not the above SQL seems to work but if the column has a, in it fails on that record
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Author,
Can you send out the data. So can write the query with testing.
Thank u.
Can you send out the data. So can write the query with testing.
Thank u.
ASKER
Sure
Table A
Table B
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
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
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.
Output
Hope it helps!
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
Output
/*------------------------
OUTPUT
------------------------*/
Id CustomerID ClientID total
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------
45 cjones12@leomail.tamuc.edu defjam903 900.0000
49 lacybee defjam903 2076.6000
(2 row(s) affected)
Hope it helps!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.