Insert SUM total into another table group by year SQL server

Question

1.00/5 (1 vote)

See more:

I have..
Table_A
Table_B

SQL

INSERT INTO Table_B (Price, Modified)
SELECT SUM(Price) AS Sales, YEAR(Modified) YEAR
FROM Table_A
GROUP BY YEAR(Modified) ORDER BY YEAR

I need as this result

Name - Price - ModifiedDate
--------------------------------
nn - 1000 - 2015
cc - 5000 - 2016 total of days and months for 2016 year
but insert this total into another table as is.
if I have to use Fetch courser also dose not matter

but I need this result
nn - 1000 - 2010
cc - 5000 - 2013
nn - 1000 - 2015
cc - 5000 - 2016
YEAR only without month and days
thank you..

What I have tried:

SQL

SELECT SUM(Price) AS Sales, YEAR(ModifiedDate) YEAR
FROM Table_a
GROUP BY YEAR(ModifiedDate) ORDER BY YEAR

BUT this SUM at run time only with out inserting into Table_B

I tryed also

SQL

SELECT SUM(Price ) AS Sales, DATEPART(YYYY, ModifiedDate) YEAR
FROM SalesDate
GROUP BY YEAR(ModifiedDate) ORDER BY YEAR(ModifiedDate)

SQL Manager
showing this error...
Msg 206, Level 16, State 2, Line 1
Operand type clash: int is incompatible with date

Posted 27-May-17 1:06am

Maher_ITbv

Updated 12-Dec-21 22:53pm

User 11060979

v5

Add a Solution

Comments

[no name] 27-May-17 7:12am

Looks like you Need to cast the year to DATETIME

Maciej Los 28-May-17 5:00am

Unclear... Please, provide example data (table_a).

an0ther1 28-May-17 17:55pm

As 0x01AA has advised - I would guess you need to revisit your table structure.
DATEPART(yyyy, <datevalue>) returns an Integer, not a Date
YEAR(<datevalue>) also returns an Integer, not a Date
If Table_B.Modified has a data-type of Date you would need to convert your Integer to a Date or change the data-type of the Modified column to an Int

Kind Regards

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 7870345 · Answer 1 · 2017-05-28T20:42:00

I am not sure about what do you intent. I think that the second column of the insert "must be" a date, and the YEAR(...) returns a integer, so a error succeds.

Please try to use:

INSERT INTO table_b(Price, Modified) <br />
SELECT SUM(price) as sales, convert(datetime,CONVERT(char(4),YEAR(Modified))+'-01-01T00:00:00') as Modified from table_a<br />
group by convert(datetime,CONVERT(char(4),YEAR(Modified))+'-01-01T00:00:00')<br />
order by 2

.

Explanation:
Year(Modified) return a int with the year of modified, but CONVERT(char(4),YEAR(fecha)) converts this year a string (for example "2017").
Then CONVERT(char(4),YEAR(Modified))+'-01-01T00:00:00', is a string that represents the first day of that year at the 0:00 hours (that is "2017-01-01T00:00:00".
For last convert(datetime,CONVERT(char(4),YEAR(Modified))+'-01-01T00:00:00') converts that string to a datetime corresponding to the first day of the year.
(Note that kind of string aaaa-mm-ddThh:mm:ss doesn´t depends of the culture or idiom so always is interpreted correctly).