Sql: group by, sum, update

Question

0.00/5 (No votes)

See more:

, +

I am passing a DataTable to a stored procedure to populate my SQL table.
There are thousands of rows. I am trying to summarize 5 of the columns, so that the thousand of rows, turn into 20 rows. Because there are 20 unique "KjøretøyID's" in [KjøretøyID].

I am trying to incorporate the following into my stored procedure:

- GROUP BY [KjøretøyID] column.
- SUM cells in [Betaling (brutto)] [Betaling (netto)] [Bestillingsgebyr (netto)] [Betalingsgebyr (netto)] [Oppdragsgebyr (netto)]
- Somehow use UPDATE? To re-configure the other columns who aren't getting added. So that my end-result are 20 rows, one per unique "KjøretøyID".

What I have tried:

This is my stored procedure.
It's using a User-Defined Table Type as temporary storage.

C#

/****** Object:  StoredProcedure [dbo].[spGetTrips]    Script Date: 17-Sep-21 12:59:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spGetTrips]
	@trips udtTripsPerMonthX readonly

AS
BEGIN
	SELECT 'Not inserted:', *
		FROM @trips AS o
		WHERE EXISTS (
			SELECT 1
				FROM dbo.tblTripsPerMonth AS i
				WHERE i.KjøretøyID = o.KjøretøyID
				  AND i.[Betaling (netto)] = o.[Betaling (netto)]
				  AND i.LøyvehaverFakturaID = o.LøyvehaverFakturaID
		);

	INSERT INTO dbo.tblTripsPerMonth([KjøretøyID], [År], [Måned], [Betaling (brutto)], [Betaling (netto)], [Bestillingsgebyr (netto)], [Betalingsgebyr (netto)], [OppdragsMVAkode], [LøyvehaverFakturaID], [Oppdragsgebyr (netto)], [OppdragsgebyrMVAkode], [RidelRegionFakturaID])

		SELECT	[KjøretøyID], 
				SUBSTRING([År], 7, 4), 
				SUBSTRING([Måned], 4, 2), 
				SUM([Betaling (brutto)]) AS [Betaling (brutto)], 
				SUM([Betaling (netto)]) AS [Betaling (netto)], 
				SUM([Bestillingsgebyr (netto)]) AS [Bestillingsgebyr (netto)], 
				SUM([Betalingsgebyr (netto)]) AS [Betalingsgebyr (netto)], 
				[OppdragsMVAkode], 
				CONCAT(LøyvehaverFakturaID, + 'UF-' + SUBSTRING([År], 9, 2) + SUBSTRING([Måned], 4, 2)), 
				SUM([Oppdragsgebyr (netto)]) AS [Oppdragsgebyr (netto)], 
				[OppdragsgebyrMVAkode], 
				CONCAT([RidelRegionFakturaID], + 'UF-' + SUBSTRING([År], 9, 2) + SUBSTRING([Måned], 4, 2))

			FROM @trips AS o
			WHERE NOT EXISTS ( 
				SELECT 1
					FROM tblTripsPerMonth i
					INNER JOIN @trips o
						ON i.KjøretøyID = o.KjøretøyID
						AND i.[Betaling (netto)] = o.[Betaling (netto)]
						AND i.LøyvehaverFakturaID = o.LøyvehaverFakturaID
			)

			GROUP BY [KjøretøyID], [År], [Måned], [OppdragsMVAkode], [LøyvehaverFakturaID], [OppdragsgebyrMVAkode], [RidelRegionFakturaID]


	UPDATE tblTripsPerMonth
	SET [OppdragsMVAkode] = (
		SELECT [MVAsats]
		FROM [tblMVAkoder]
		WHERE [ID] = 'MVAkode2'
		);

	UPDATE tblTripsPerMonth
	SET [OppdragsgebyrMVAkode] = (
		SELECT [MVAsats]
		FROM [tblMVAkoder]
		WHERE [ID] = 'MVAkode5'
		);
		
	UPDATE tblTripsPerMonth
	SET [Oppdragsgebyr (netto)] = (

		SELECT [Betaling (brutto)] * 8 / 100
	);
END

This is essentially what I need:

chart — ImgBB[^]

Posted 16-Sep-21 3:22am

Flidrip

Updated 17-Sep-21 3:04am

v5

Add a Solution

Comments

Wendelius 16-Sep-21 14:36pm

Can you post an example of the source data and the desired result?

Flidrip 17-Sep-21 3:14am

@Wendelius, thank you for taking the time. I have updated my question with a image showing what I need.

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)

CHill60 · Accepted Answer · 2021-09-17T01:26:00

When summarising data in a SELECT statement you would never use UPDATE. You are doing one or the other not both (although you can update a table based on the results of a SELECT, but the image does not imply that that is what you are trying to do).

From the image provided it looks like a straight forward "GROUP BY"

SQL

SELECT 
   SUM([Betaling (brutto)]) as [Betaling (brutto)]
   ,SUM([Betaling (netto)]) as [Betaling (netto)]
   ,SUM([Bestillingsgebyr (netto)]) as [Bestillingsgebyr (netto)]
   ,SUM([Betalingsgebyr (netto)]) as [Betalingsgebyr (netto)]
   ,SUM([Oppdragsgebyr (netto)]) as [Oppdragsgebyr (netto)]
FROM 
   tblTripsPerMonth
GROUP BY [KjøretøyID], [År], [Måned]

That will give you one row per [KjøretøyID], per [År] and per [Måned]. With the data you provided it will give the results on the right.

A couple of other points.
- Stick to English or Anglicized names for columns and avoid spaces and things like (brutto). Having to put Square Brackets around every column quickly becomes very tiring.
- When giving sample data here in QA don't use images. Just provide the code that will populate the sample data (with the value!) or at least provide that table in text format here so we can copy it easily.
- Same goes for the expected results - I'm at work in my lunch break and do not like following links outside this site just to help someone in my spare time
- Try to stick to just the relevant detail - it doesn't matter that this is a Stored Procedure and it doesn't matter that you are using a user-defined table type - you are essentially just trying to summarise some data. Small steps.

Edit after OP comments:
It seems that you only really want to group by [KjøretøyID] so try this instead

SQL

SELECT [KjøretøyID]
   SUM([Betaling (brutto)]) as [Betaling (brutto)]
   ,SUM([Betaling (netto)]) as [Betaling (netto)]
   ,SUM([Bestillingsgebyr (netto)]) as [Bestillingsgebyr (netto)]
   ,SUM([Betalingsgebyr (netto)]) as [Betalingsgebyr (netto)]
   ,SUM([Oppdragsgebyr (netto)]) as [Oppdragsgebyr (netto)]
FROM 
   tblTripsPerMonth
GROUP BY [KjøretøyID]

That is going to give you the results

AG4203000002     5699     5376.40    147.16    80.62
AG4203000003     1190     1122.64     22.64    19.84

Notice that [År] and [Måned]. If you want to include those extra columns in the summary, you cannot just add them to the Group By clause, you also need to add them to the Select ... and from what you are saying in the comments that is returning too many rows.

Perhaps include some sample data where [År] != 2021 and/or [Måned] != 7 ... work out your expected results from that revised sample and share that.