Click here to Skip to main content
14,388,816 members
Rate this:
Please Sign up or sign in to vote.
See more:
hi all, im quit new in the buisness and i have a query and the result is value per day from 4 different samples. now in the same query i would like to add all the value on the same day and save as a new column name. can anyone help me finisch my query.

here is the result:
its a tagname day of the month and value
0200_P1511_TPV	27	762
0200_P1521_TPV	27	2492
0200_P3010_TPV	27	2927
0200_P3020_TPV	27	2337
this is the query that gives me this result:

SELECT
parameterColumn,

DATEPART (DD, dateTimeColumn) AS day1,

SUM(CONVERT(float,valueColumn)) as value


FROM [dbo].[mydbname]
where [dateTimeColumn] > '2019-05-19 08:00:00.000'
 and ( [parameterColumn] = '0200_P1511_TPV'
  or [parameterColumn] = '0200_P1521_TPV'
  or [parameterColumn] = '0200_P3010_TPV'
 or [parameterColumn] = '0200_P3020_TPV')
   
   
Group By DATEPART (Day, dateTimeColumn), [parameterColumn]

order by [day1] desc
Now I would like a new column where all the value of day 27 and all other days are summed up.


best regards, emilio

What I have tried:

What I have written in the problem description
Posted
Updated 28-May-19 7:05am
v2
Comments
CHill60 28-May-19 7:34am
   
Can you give us some sample data and expected results in table format?
Also your where clause can be simplified to
where [dateTimeColumn] > '2019-05-19 08:00:00.000'
 and [parameterColumn] IN ('0200_P1511_TPV','0200_P1521_TPV','0200_P3010_TPV','0200_P3020_TPV')
emilio1979 28-May-19 9:05am
   
hi, thnx for the reply,

here som extra data:
0200_P1511_TPV 27 762
0200_P1521_TPV 27 2492
0200_P3010_TPV 27 2927
0200_P3020_TPV 27 2337
0200_P1511_TPV 26 1206
0200_P1521_TPV 26 2605
0200_P3010_TPV 26 2675
0200_P3020_TPV 26 3286
0200_P1511_TPV 25 951
0200_P1521_TPV 25 2569
0200_P3010_TPV 25 3002
0200_P3020_TPV 25 2635
0200_P1511_TPV 24 851
0200_P1521_TPV 24 2539
0200_P3010_TPV 24 2195
0200_P3020_TPV 24 3262
0200_P1511_TPV 23 781
0200_P1521_TPV 23 2563
0200_P3010_TPV 23 3083
0200_P3020_TPV 23 2340
0200_P1511_TPV 22 943
0200_P1521_TPV 22 2545
0200_P3010_TPV 22 2311
0200_P3020_TPV 22 3095


i would like to get the data sorted by day not every sample per day:

so totals per day like this:

sum off samples day 27 27 8518
sum off samples day 26 26 9772
sum off samples day 25 25 9075
sum off samples day 24 24 9481
sum off samples day 23 23 9772
sum off samples day 22 22 9157
CHill60 28-May-19 11:12am
   
I didn't see your reply - you need to use the "Reply" link next to comments so that the member is notified.
That looks like further results data from your query - it's the data on the table I would like to see
emilio1979 29-May-19 2:15am
   
hi, hereby the top 10 query on the Original table:
dateTimeColumn locationColumn parameterColumn valueColumn unitColumn flagColumn commentColumn qualifierColumn dateTimeOrgColumn
2019-02-12 01:45:00.000 0400CLUSTER 0400_FT1100_MTW 125.011 m3/h 1 Meetwaarde 100.00 2019-02-12 01:45:00.000
2019-02-14 05:15:00.000 0400CLUSTER 0400_FT1100_MTW 85.868 m3/h 1 Meetwaarde 100.00 2019-02-14 05:15:00.000
2019-02-18 14:30:00.000 0400CLUSTER 0400_FT1100_MTW 709.544 m3/h 1 Meetwaarde 100.00 2019-02-18 14:30:00.000
2019-02-21 21:15:00.000 0400CLUSTER 0400_FT1100_MTW 902.572 m3/h 1 Meetwaarde 100.00 2019-02-21 21:15:00.000
2019-02-25 07:30:00.000 0400CLUSTER 0400_FT1100_MTW 595.224 m3/h 1 Meetwaarde 100.00 2019-02-25 07:30:00.000
2019-03-01 23:30:00.000 0400CLUSTER 0400_FT1100_MTW 546.001 m3/h 1 Meetwaarde 100.00 2019-03-01 23:30:00.000
2019-03-06 14:45:00.000 0400CLUSTER 0400_AT1330_MTW 4.802 g/l 1 Meetwaarde 100.00 2019-03-06 14:45:00.000
2019-03-06 14:45:00.000 0400CLUSTER 0400_AT1340_MTW 2.633 mg/l 1 Meetwaarde 100.00 2019-03-06 14:45:00.000
2019-03-06 15:00:00.000 0200CLUSTER 0240_LT1010_MTW 19.173 % 1 meetwaarde 100.00 2019-03-06 15:00:00.000
2019-03-06 15:00:00.000 0200CLUSTER 0250_LT1010_MTW 31.361 % 1 meetwaarde 100.00 2019-03-06 15:00:00.000
CHill60 29-May-19 3:36am
   
Thank you - I'll have a look later today

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

If you're actually interested in "learning SQL" instead of just asking for "the answer", you should learn to decompose "complex" queries into multiple simpler "chained" queries in order to understand what is going on.

https://www.datacamp.com/community/tutorials/sql-tutorial-query[^]
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100