October 30, 2015 at 9:40 am
Hi,
in the table i have
Date col1 col2 name
01/10/2015 50 13 personA
01/10/2015 50 11 personA
03/10/2015 50 21 personA
01/10/2015 50 17 personb
using matrix
by name
col1|col2
date
I get:
personA PersonB
01/10/2015 100 24 50 17
03/10/2015 50 21 - -
the total I want is for col1 for personA to give 100 (50+50 , meaning 50 per day).
I know grouping should aggregate. But can you please help with this?
Thanks
October 30, 2015 at 9:52 am
What are you trying to get in your report? That part's not totally clear. What does the second number mean?
October 30, 2015 at 10:05 am
Well, in the sql table given. A user has different tasks, and col2 is suppose to represent seconds in task, but it was added another column with the total per day.
So, John, spent let's say 5000seconds in a Task and 10 000 in another. That will give two different entries.
But there is also a field with the total seconds spent in the day 28 000 secs.
So
John
on the 01/10/2015
secs per task, secs per day
5000 28 000
10000 28 000
this means that the total in 01/10/2015 for John is 28000 secs, but the total of tasks he performed are 15 000 secs
This was poorly done, but the 28 000 repeats itself.
In the table it is requested to show in the total of secs per day the sum of days in secs, so if he worked 3 days, would be 3x28000
but since you have several tasks, it is giving the total of records x 28 000
I know it's confusing, this isn't my task.. I'm just trying to help a coleague.
October 30, 2015 at 10:31 am
maybe something like this?
October 30, 2015 at 11:29 am
no, sorry. can't be a simple sum.
October 30, 2015 at 12:05 pm
Welcome to the forum. In the future please post some DDL code to help us help you. Trust me you'll get help much faster.
Read this excellent article on how to post questions: Click Here[/url]
Here is your freebie. 😉
DECLARE @myTable TABLE (SomeDate DATE, Col1 INT, Col2 INT, Name NVARCHAR(10))
INSERT INTO @myTable
VALUES
('2015/10/01', 50, 13, 'personA'), ('2015/10/01', 50, 11, 'personA'), ('2015/10/03', 50, 21, 'personA'), ('2015/10/01', 50, 17, 'personB')
SELECT * FROM @myTable
October 30, 2015 at 12:11 pm
Hi, thanks.
My example had no DDL.
Regards,
Edgar
October 30, 2015 at 12:13 pm
Is this what you are looking for?
DECLARE @myTable TABLE (SomeDate DATE, Col1 INT, Col2 INT, Name NVARCHAR(10))
INSERT INTO @myTable
VALUES
('2015/10/01', 50, 13, 'personA'), ('2015/10/01', 50, 11, 'personA'), ('2015/10/03', 50, 21, 'personA'), ('2015/10/01', 50, 17, 'personB')
SELECT
SomeDate,
SUM(Col1) OVER (PARTITION BY SomeDate, name ORDER BY someDate) AS Total,
Col2,
Name
FROM
@myTable
ORDER BY
SomeDate,
name
October 30, 2015 at 12:19 pm
I have to check the results, using SQL I can fix it.
The issue was using SSRS for my colleague .
Thanks
October 30, 2015 at 12:26 pm
Or something like this...
DECLARE @myTable TABLE (SomeDate DATE, Col1 INT, Col2 INT, Name NVARCHAR(10))
INSERT INTO @myTable
VALUES
('2015/10/01', 50, 13, 'personA'), ('2015/10/01', 50, 11, 'personA'), ('2015/10/03', 50, 21, 'personA'), ('2015/10/01', 50, 17, 'personB')
SELECT
SomeDate,
SUM(CASE WHEN Name = 'personA' THEN Col1 END) [personA],
SUM(CASE WHEN Name = 'personB' THEN Col1 END) [personB]
FROM
@myTable
GROUP BY
SomeDate
October 30, 2015 at 12:29 pm
edfimasa (10/30/2015)
I have to check the results, using SQL I can fix it.The issue was using SSRS for my colleague .
Thanks
You are right in that you can't just do a simple SUM. Either way you just need to make sure you sort your data to SUM the results by date AND person. In the end that should give you what you are looking for.
October 30, 2015 at 12:43 pm
Is it possible to sum the sub totals of a group without reading the whole table in BIDS?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply