If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Sql help

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.

Code:

select DEPARTMENT,
CASE
WHEN ((DATE >= '06/01/2014') AND (DATE <= '06/30/2014')) THEN (cast(sum(Amount) as decimal(10,2)))
ELSE '' END AS 'JUNE',
CASE
WHEN ((DATE >= '07/01/2014') AND (DATE <= '07/30/2014')) THEN (cast(sum(Amount) as decimal(10,2)))
ELSE '' END AS 'JULY'
from L27_PHAROS_DETAIL_DATA
WHERE Department = 'TECHNOLOGY SERVICES'
GROUP BY DEPARTMENT, DATE
ORDER BY DEPARTMENT

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?

Code:

SELECT department
, 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
FROM L27_PHAROS_DETAIL_DATA
WHERE department = 'TECHNOLOGY SERVICES'
GROUP
BY department
ORDER
BY department

Another clean-up

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,
SUM(CASE
WHEN foobar_date BETWEEN '2014-06-01 AND '2014-06-30'
THEN foobar_amt) ELSE 0.00 END AS june_foobar_tot,
SUM(CASE
WHEN foobar_date BETWEEN '2014-07-01 AND '2014-07-30'
THEN foobar_amt) ELSE 0.00 END AS july_foobar_tot
FROM Pharos_Details_127
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.