Hi
I have table and the column as ID, orderID, Uprice, weight ...upto 25 column.
ID, orderID, Uprice, weight
1 123 5.50 100
2 327 8.90 350
3 625 5.70 200
4 123 11.80 345
How do I sum the total of UPRICE group by orderID and select rest of the columns as well. ( SUM only the Uprice)
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName Clm ABC-X
X
Page2:
GroupName Clm ABC-Y
Y
Page3:
GroupName Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:
Year
Month
Date
And the following row groups:
Region
Product
SubType (hidden, data at the date level is summed to Product)
At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)
I have Table Like this
t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail
[Code] ....
i dont't know how to select row with max column value group by another column. I have T-SQL
CREATE PROC GET_USER AS
BEGIN
SELECT T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] ,
MAX(T.[Second Start Date]) AS [Max Second Start Date],
T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME
FROM (
[code]....
Hi,
Say I have the table below:
Tabl1
PKey ColA ColB ColC
1 a1 b1 c1
2 a1 b2 c2
3 a1 b3 c3
... a2 ...
...
... a3 ...
...
If I group by ColA with the select statement as follows:
SELECT
ColA, MAX(ColB),
FROM
Tabl1
GROUP BY
ColA
If for example for a1 the MAX(ColB) is b2, THEN I would like to include ColC in the select statement such that for a1 the resulting row would be:
ColA ColB ColC
a1 b2 c2
What is the best way to do this?
TIA!
I have a column which Data Type is DateTime the value is looks like 2008-01-18 16:40:39.560if I want to group by this column only compare with yyyy/mm/dd , don't care the TimeHow to write a Sql Query to fit this request?the values in Columns like below2008-01-18 16:40:39.5602008-01-18 16:40:39.5602008-01-18 16:40:39.5602008-01-18 16:40:39.5602008-01-18 17:10:02.8572008-01-18 17:10:37.3732008-01-18 17:10:37.3732008-01-18 17:11:57.1872008-01-18 17:12:22.8102008-01-18 17:13:01.4502008-01-18 17:13:37.1532008-01-18 17:13:52.4502008-01-18 17:14:10.6402008-01-18 17:14:24.6532008-01-18 17:14:41.1532008-01-18 17:15:09.3572008-01-21 14:55:18.560 thank you for your posting .
View 3 Replies View RelatedI have 2 tables which have the data as follows:
A:
Id Reason Amount Subject RecordNo
1 Gift 100 first 11
2 Gift Reason 200 second 12
3 Gift Reason 100 first 11
The result that I want is :
Reason Amount Subject
ALL 200 first
i used the following query
select
case when Grouping(B.Subject) <> '' then 'ALL' else B.Subject End as Subject ,
case when Grouping(B.reason) <> '' then 'ALL' else B.Reason END as Reason,
SUM(B.amount) as amount
from B
where B.RecordNo = 11
group by grouping sets (B.Reason,B.Subject)
but it's giving me 3 rows in return.
I want only one summation row. How can i get that?
Dear experts,
Hi, please do give me your expert advise and opinon on this matter:
I have a table name PerformaceRecords with a few columns, one of which is performance banding.
i.e.
PerformanceBanding
------------
Outstanding
Good
Average
Good
Poor
When I use a group by clause, i.e. Select PerformanceBanding, Count(PerformanceBanding) as ResultCount from PerformanceRecords group by PerformanceBanding
I got the result as
PerformanceBanding ResultCount
---------------------------------
Good 2
Poor 1
Average 1
Outstanding 1
What I want to get is the PerformanceBanding as columns and the Result as rows
i.e.
Good Poor Average Outstanding
----------------------------------
2 1 1 1
how do I go about modifying my SQL select statement to achieve this result?
Thank you in advance for assisting me.
Hello!
This is not really a question, maybe a start of discussion if this could be a feature in coming versions...
Let's say I have a table like this:
id text1 text2
int varchar varchar
1 abc ghi
2 abc ghi
3 def ghi
and I want to query using group by, I would write SELECT text1, text2 FROM table GROUP BY text1, text2
This is not too bad, but imagine I got several columns of text I need to query, then I need to put them in both places, SELECT and GROUP BY. If I do use functions like SUBSTRING or concatenation on the columns, I need to repeat these in the GROUP BY as well.
It would be nice if I could say "GROUP BY id", and since id is unique on the table it is obvious that all columns taken from this table are already grouped and can be queried without having to have them in group as well.
For functions this is of course only possible if all parts only refer to columns from this table, or absolute entries, like LEFT(varchar ,3) or int+1 or such.
With this example, the benefit is not visible: if I added the id to the GROUP BY, I get every single row of course. GROUP on unique is useless... It does make sense if you query another table and join in this one as a lookup, and the id gets "multiple" this way.
As I said, just an idea to make the GROUP BY section more transparent and avoid repeating sections!
I'd like to know what you think of that.
Ralf
Hi All,
Please let me know if both query fetch the same result
Select Col1, Col2, Col3, Col4 from Table1 Group By Col1, Col2, Col3, Col4
Order By Col1, Col2
Select Col1, Col2, Col3, Col4 from Table1
Order By Col1, Col2
Thanks,
Muthu
Hello
I have a Table
Code Name Gender DateJoined
Now when i Group by Name i get
Name Male Female
jasldk 1 2
But the problem i have is i need to filter the records based on Date joined, but i cant include the DateJoined in my Group by clause ( i am making the Groupby As View) and since my view dont contain the date i couldnt use that as view to use in my application.
Is there any other way i can achieve this?
Thanks in advance
'
With Regards
How can we write the query using groupby. I need to have group by only one column. Is it possible through subquery?
select col1, col2, col3
from testtable
group by col1.
I´m looking to create a select where I sum the daily_return by stock_code, and then I would like to have an additional column containing the most recent "rating" available by date
So if you where to execute the query below, the result would be:
stock1 0.54 3
stock2 0.05 1
Here is what I have so far:
DECLARE @stock_returns TABLE
(
stock_code VARCHAR(10) NOT NULL,
date1 DATE NOT NULL,
daily_return NUMERIC(10, 2) NOT NULL,
rating numeric (6,0) not null);
[Code] .....
Please advise on how to get the GROUP BY coded in an acceptable way:DECLARE @LO INTDECLARE @HI INTDECLARE @StartDate varchar(10)DECLARE @EndDate varchar(10)SELECT @StartDate = '01/01/2005'SELECT @EndDate = '06/30/2005'SELECT @LO= 250SELECT @HI= 333SELECTStateCD, CountyCD, Zip, Z.CityName, Z.StateCode, Z.CountyName, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@LO) + '-' +Convert(varchar(4),@HI), 'StartingDate' = @StartDate, 'ThruDate' = @EndDate, JumboAmount = SUM(JumboAmount), JumboMortgages = SUM(JumboMortgages), JumboFIXMortgages= SUM(JumboFIXMortgages), JumboFIXAmount = SUM(JumboFIXAmount), JumboARMMortgages = SUM(JumboARMMortgages), JumboARMAmount= SUM(JumboARMAmount)FROM LoanDetails T INNER JOIN dbo.ZipCodesPreferred ZON T.StateCD = Z.FIPS_State AND T.CountyCD = Z.FIPS_County AND T.Zip =Z.ZipCodeGROUP BYStateCD, CountyCD, Zip, Z.CityName, Z.StateCode, Z.CountyName, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@LO) + '-' +Convert(varchar(4),@HI), 'StartingDate' = @StartDate, 'ThruDate' = @EndDate
View 2 Replies View RelatedI have a table of users and date when they logged on to a system. I am trying to count how many distinct users logged on for each day of the week. The SQL below works when there's at least a user for each day. But when there is no user for a particular day such as Sunday, I still want it to return "SUN
0 "
I learned that you can use GROUP BY ALL and it works but the "ALL" is deprecated beyond SQL 2005.
------------------------------------
SELECT UPPER(LEFT(DATENAME(dw, StartTime), 3)) AS DayOfWeek,
COUNT(DISTINCT UserID) AS NumberOfUser
FROM testUserLoginDuration
WHERE Archived = 0
GROUP BY UPPER(LEFT(DATENAME(dw, StartTime), 3))
ORDER BY
CASE WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'MON' THEN 1
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'TUE' THEN 2
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'WED' THEN 3
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'THU' THEN 4
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'FRI' THEN 5
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SAT' THEN 6
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SUN' THEN 7
END
--------------
returns
MON 6
TUE 3
WED 5
THU 3
FRI 2
SAT 1
I have a table with 5 levels of grouping and a detail row. When the report first displays it looks like this:
Group1 Hdr Group2 Hdr Group3 Hdr Group4 Hdr Group5 Hdr Detail Headers
+Group1-Item1
+Group1-Item2
+Group1-Item3
How can I alter the visibility of the group headers when they don't have access into the scope of the groups themselves ? At startup, I only want to show group1's header. If I expand an item in group 1, then I only want to show headers for group 1 and 2, etc ...
Thanks
I just can not understand why when I add text to a group header the report displays more group row space but when I export to excel the extra space disappears....
What the....Help Microsoft please explain...
Thank You...
I have a query similar to the following, but it gives the following error:
'Invalid column name 'GroupName'.'
SELECT 'Primary' as GroupName, City
FROM PrimaryTable
UNION ALL
SELECT SpecialGroupName AS GroupName, City
FROM Table2
GROUP BY GroupName, City
GroupName is varchar(30)
Any ideas why this would fail. Thanks in advance.
Terry
I have a table which I would like to format like so:
From this:
CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[header] [varchar](50) NULL,
[citation] [varchar](200) NULL,
CONSTRAINT [PK_Table_1_1] PRIMARY KEY CLUSTERED
[Code] ....
Just as a single varchar(max) field.
I've got a fairly standard query that does a group by a type column, and then sums the lengths of a VARCHAR column. I'd like to add into that a concatenated version of the string always concatenating in primary key order. Is that possible?
View 1 Replies View RelatedHi,
how to change visibility of a column group in a matrix?
Thanks,
Igor
I am using Reporting Services 2000. If you find out that Reporting Services 2005 would resolve this issue, please lemme know also. But I want to mention that I would prefer a way to fix this without changing Reporting Services versions.
I have a table that has a group on ProductTypes. This group is set to PageBreak at end.
What I need to do is to conditionally hide an entire column based on the current group's ProductType.
Examples
Page 1 = Product 1 = Column 1 is visible
Page 1 = Product 1 = Column 2 is visible
Page 2 = Product 2 = Column 1 is hidden
Page 2 = Product 2 = Column 1 is visible
Can you help me figure this one out ? I've tried everything I found on the net, especially everything on this page : http://blogs.msdn.com/chrishays/rss.xml
Thank you in advance.
I wirte my query as:
select mydate
from mytable
group by mydate
ordre by mydate
The datatype of mydate column is DateTime. The result is showing me same repeating groups e.g.
2/27/2008 12:00:00 AM
2/27/2008 12:00:00 AM
2/27/2008 12:00:00 AM
Please help so that my query can give me unique groups.
Regards,
Professor Corrie.
Hi all.
I've created a horizontal report using a matrix according to the quite useful article Horizontal Tables, but there's one thing I'm trying to do that I'm not having any luck figuring out how to do, and that is creating a footer column for a column group that contains a sum of the values in some of its columns.
In other words, here's how I would like to have it look...
Jan
Feb
Mar
Quarter 1 Total
Apr
May
Jun
Quarter 2 Total
Tier 1
$100
$100
$100
$300
$0
$0
$100
$100
Tier 2
$50
$50
$0
$100
$100
$100
$100
$300
The fields in the dataset are Month, Tier1, Tier2, Year and Quarter. I've created a group on Year & Quarter, tried right clicking and selecting Subtotal, but that seems to add a subtotal column after every month, not at the end of the quarter group as I want.
Any ideas on what I need to do? Is this even possible?
TIA
Hello Friends,
I am creating a report in which I want to create group column using two field value. Is it possible to do so? We have a requirement in which we are fetching data from two different hierarchy.
A B C D E F G H I J K
L 1 2 3....................4 5
M .............................
N .........................
O .......................
P
The report matrix look like the above one. The elements A,B,C are coming from one hierarchy and D,E,F,G,H,I,J,K are coming from other hierarchy. But i have created one data set to fetch the values for the report. But while creating the column group I am getting both two diff fields so I am not able to use it in single Column group and I want to use only one column group.
Can anybody help me out to solve this issue?
I've done this before, but i cant remember how.
All i want to do is sort this group in a specific way.
I want them to be in this order:
AME01
ASE01
ACO01
ALU01
AOS01
APH01
ATR01
ATE01
ACR06
ACR05
ACR02
ACR03
ACR08
ACR07
What is the code i would put in the sorting expression, for this to take place?
Hello,
I'm just starting to analyze with SQL 2005 reporting services and trying to build a test report using the report wizard.
This is how i've got it in SQL reports now:
Opportunity ID Product Name Cost
--------------------- --------------------- ----------------
1 Orange $1
1 Apple $2
1 Grapes $4
2 Orange $1
2 Apple $2
3 Orange $1
Now, what if I wanted to group them to provide subtotals like:
Order Product Name Cost
--------------------- --------------------- ----------------
1 Orange $1
Apple $2
Grapes $4
-------------------------------
SubTotal $7
2 Orange $1
Apple $2
-----------------------------
SubTotal $3
3 Orange $1
------------------------------
SubTotal $1
The problem that i'm having right now is on the layout designer somehow programming the subtotals based on the IDs.
Am i thinking in the right direction that I should be using the layout designer to do this? Or should this be done programmatically?
Thanks.
Mike
I am having some difficulty writing a relatively basic query. The objective is to retrieve the new stories (headlines) for the past 3 days from the database. Since each headline can be assigned multiple categories (topics) the query returns a row for every headline assignment. I can't use the 'Group By' expression because one of the columns is nText.
So basically if there is an article written yesterday, "I Love Cats" that gets assigned both topics 'CATS' and 'PETS' I only it returned with the first topic assigned... 'CATS'. Here is a little image of the three tables being called:
http://64.225.154.232/temp_dbDiagram.gif
I don't think that this query is too difficult, but I'm just getting my feet wet with writing queries that are more than select * from whatever. Any insight or recommendations are greatly appreciated.
SELECT headline.HEADLINE_ID, headline.HEADLINE_TITLE, headline.HEADLINE_DATE, headline.HEADLINE_THUMBNAIL,
topic.TOPIC_NAME, topic.TOPIC_URL
FROM tbl_CCF_Headlines headline INNER JOIN
tbl_CCF_Headlines_Topics ON headline.HEADLINE_ID = tbl_CCF_Headlines_Topics.HEADLINE_ID INNER JOIN
tbl_CCF_Topics topic ON tbl_CCF_Headlines_Topics.TOPIC_ID = topic.TOPIC_ID
WHERE (headline.HEADLINE_DATE IN
(SELECT TOP 3 HEADLINE_DATE
FROM tbl_CCF_HEADLINES
GROUP BY HEADLINE_DATE
ORDER BY HEADLINE_DATE DESC))
ORDER BY headline.HEADLINE_DATE DESC
I have column which stores People count based on department, Now I want to keep them in the batch of 1000, If the running summary of (No of people) from departments reached 1000 then it should start sum(no of people) from 0 to 1000
Is there any running summary kind of function which can start sum record with in range of 0-1000
For Ex. My Data stored like this
Dept People Count
CSE 200
IT 250
EEE 312
ECE 214
MEC 337
Batch Grouping
Dept People Count BatchSum
CSE 200 200
IT 250 450
EEE 312 762
ECE 214 976
MEC 337 337 (Note here since its crossing 1000, its resetting and starting summary)
I implemented this with While Loop & if condition, But its very slow, is there any other way to achieve it in better way.
I need to convert the column to rows but within group. example
Group Name Value
p a 1
p b 2
p c 3
p d 4
q a 5
q b 6
q d 7
r a 8
r b 9
r c 10
r d 11
This need to be transposed to :
Group a b c d
p1234
q56NULL7
r891011
Please zoom your browser to 200% to make the images clearer. Notice that the left and right edges of the parent Month column in the second example are 2pt black and the inner edges of Forecast, Budget and Actual are 1 point light gray.
View 4 Replies View RelatedHello Guys,
I am working on a matrix report which has several row groups and 1 column group. After execution, the column group wil end up with several columns containg numeric counts. I would like to have the grand total for each "column group" column as a last row on this report.
For row groups you can just right click "Subtotal", but that is not possible for column group. Could someone please help me to find a clever way of accomplishing this, please. Thank you so much for your help!