I have a sql code that I am having some difficulty with. All I'm trying to do is get each department and sum the amount by month based on the date in the table. I am getting "error coverting data type varchar to numeric".
Any help is appreciated.
WHEN ((DATE >= '06/01/2014') AND (DATE <= '06/30/2014')) THEN (cast(sum(Amount) as decimal(10,2)))
ELSE '' END AS 'JUNE',
WHEN ((DATE >= '07/01/2014') AND (DATE <= '07/30/2014')) THEN (cast(sum(Amount) as decimal(10,2)))
ELSE '' END AS 'JULY'
WHERE Department = 'TECHNOLOGY SERVICES'
GROUP BY DEPARTMENT, DATE
ORDER BY DEPARTMENT
I hope the column isn't really called date... that's a reserved word!
Also, when writing dates ALWAYS use the format YYYY-MM-DD. This way there can be no confusion as to what it means e.g. UK vs US formats.
In your case statement you are mixing data types a fair bit... decimal in one part and a string value in the else condition?
, Sum(CASE WHEN "date" >= '2014-06-01' AND "date" < '2014-07-01' THEN amount ELSE 0 END) As june
, Sum(CASE WHEN "date" >= '2014-07-01' AND "date" < '2014-08-01' THEN amount ELSE 0 END) As july
WHERE department = 'TECHNOLOGY SERVICES'
Thanks for your help Gvee....I understand what you did in your code. I have one other question please.
I have the same similar code:
select distinct Department, Name, [Transaction Type], sum(quantity) as Quantity, [Quantity Name], (cast(sum(Amount) as decimal(10,2))) as Amount
WHERE DATE1 >= '2014-06-01' AND DATE1 < '2014-07-01'
AND Department = 'Technology Services'
group by Department, Name, [Transaction Type],[Quantity Name]
order by Department, Name, [Transaction Type]
The output from the above code looks like:
Department Name Transaction Type Quantity Quantity Name Amount
TECHNOLOGY SERVICES User1 Print 709 Pages 32.76
TECHNOLOGY SERVICES User1 Copy 22 Prints 0.88
TECHNOLOGY SERVICES User1 Scan 260 Pages 0
TECHNOLOGY SERVICES User2 Print 515 Pages 33.48
TECHNOLOGY SERVICES User2 Copy 62 Prints 4.32
TECHNOLOGY SERVICES User2 Scan 46 Pages 0
Is there a way using sql or plsql to get the results to look something like:
Please follow basic Netiquette and post the DDL we need to answer this. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. Avoid dialect in favor of ANSI/ISO Standard SQL. We need to know the data types, keys and constraints on the table.
DATE is a data type in SQL, and you can use BETWEEN with it. We do not use CAST to get display formats in SQL that was COBOL. We never start a data element name with a digit that is ISO Standards and common sense, and we do not add data to a table (what is always in a table?)
I think this is what you meant:
SELECT dept_name, foobar_date,
WHEN foobar_date BETWEEN '2014-06-01 AND '2014-06-30'
THEN foobar_amt) ELSE 0.00 END AS june_foobar_tot,
WHEN foobar_date BETWEEN '2014-07-01 AND '2014-07-30'
THEN foobar_amt) ELSE 0.00 END AS july_foobar_tot
WHERE dept_name = 'technology services'
GROUP BY dept_name, foobar_date;
Today, we do not use ORDER BY so much in serious apps. The results get passed to a presentation layer server and it does any sorting. The database server is kept as clean and fast as possible.
The totals are now numeric, not COBOL strings. If you wish, you can make a zero into a NULL, but that seems wrong on the face of it.