TOP X Group Rows

May 31, 2007

Ok, I have searched the forums and the web, and I dont think this is available, but I am going to ask for any ideas...



I need to show a parameterized TOP x report with collapsible detail. For example:



+ CA

+ NY

+ GA

+ CO

+ PA

+ All Others



Again, the user is able to select how many group rows are shown.



Any suggestions or ideas?



Thanks!!



BobP

View 1 Replies


ADVERTISEMENT

How?: Group By Date And Count Rows In Group

Jan 29, 2007

I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.

this is the best I can come up with:

Code:


SELECT
DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts
FROM log_directory_contacts
GROUP BY post_date



The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.

What am I doing wrong? Thanks!

View 9 Replies View Related

Get One Row From Each Group Of Rows

Jul 22, 2007

Hi,I'm trying to build a query that get only one row from a group ofrows, but I need the values from that row and not the results of onefunction group.I need one row for each idRef, with column2=2 and the bigger column1id |idRef | column1 | column21 1 0 12 1 1 23 1 2 14 2 0 15 2 1 26 2 2 17 2 3 2For these, I will take the rows with id=2 and id=7.Thank you, and sory for my english.

View 2 Replies View Related

How Do I Use Group By To Get Top Rows?

Sep 21, 2007

Okay, I am sure this is an easy question, but for some reason I cannot wrap my brain around it. I have a table that has data similar to the following:





IPAddress
VisitDate
URLVisited

192.154.21.554
9/18/2007
http://www.microsoft.com

192.154.21.554
9/19/2007
http://www.google.com

164.21.124.23
9/19/2007
http://www.microsoft.com

192.154.21.554
9/20/2007
http://www.yahoo.com


What I am trying to do is use a select query to find the most recent visit per IP address (but I also want the other row data like URL visited). I have something like this that is finding the most recent visit per IP address:

SELECT IPAddress, MAX(VisitDate) AS MaxDateVisited
FROM VisitorTable
GROUP BY IPAddress

How do I also pull in the URL that is associated with the MAX date visited? Thank you!

View 4 Replies View Related

Keep Group Rows Together

Dec 21, 2006

Hi,

Is it possible to keep a group in a table report on the one page if this group could be fitted into the rest of the page and start new page otherwise?

Thanks,
Igor

View 4 Replies View Related

Trying To Get The First Count Of A Group Of Rows

Dec 24, 2003

Hi, I am trying to get the first row of what might be a group of any size of rows within an id that may contain a specific field. For eg

Row A = NoteID 1, FK_JobID 1, UnRead

Row B = NoteID 2, FK_JobID 1, UnRead

Row C = NoteID 3, FK_JobID 1, UnRead

I need the sql to return to just one Job (row) even though the job as 3 UnRead fields. But its returning 3 because its only doing what I'm asking. What I need it to do is just get the one Job (row) where any of the notes = UnRead.

I tried using Top 1, but that will only ever return one row and since I need it to return more than one job (row) it won't work.

Heres my attempt

DECLARE @UserID INT

SET @UserID = 4


SELECT User_Notes.BeenRead, Master_Jobs.By_Who, Master_Jobs.Next_Action, Master_Jobs.Due_Time, Master_Jobs.Due_Date, Master_Jobs.Contact,
Master_Jobs.Job_Title, Master_Jobs.JobID
FROM User_Notes INNER JOIN
Note ON User_Notes.FK_UN_NoteID = Note.NoteID INNER JOIN
Master_Jobs ON Note.FK_JobID = Master_Jobs.JobID

WHERE Note.FK_UserID = User_Notes.FK_UN_UserID AND
BeenRead = 'UnRead'

Thanks in advance

View 6 Replies View Related

Counting Rows In A Group By...

Jul 26, 2000

Hi,

I'm using SQL Server 7.0. My problem is that I'd like to count how many rows are in a group by. For example, here is my data:

Agent Branch
1 1
2 1
1 1
2 1
1 2
1 2

If I do this:

Select Agent, Branch
From Table1
Group By Agent, Branch

I get:

Agent Branch
1 1
2 1
1 2

What I need to do is count the records (3) in the group by. I've tried a few things but I can't seem to come up with the number of rows in a group by.

Can anyone help me?

Thanks in advance,
Darrin

View 2 Replies View Related

Group Multiple Rows Into One

Jun 20, 2012

creating a query to group identical rows into one and placing corresponding data in appropriate columns for a table named items, what I mean is that I have a table structured as below

Itemcode, Description, Period1, Period2, Period3
001--------Desc--------233,---------,------- <<ROW1
001--------Desc ------------ ,100,------------ <<ROW2
001--------Desc ------,--------,-------,300 <<ROW3

Row one contains a value in Period1
Row two contains a value in Period 2
Row three contains a value in Period 3

I need to create a query so that the end result would look like this;

Itemcode, Description, Period1, Period2, Period3
001 --------Desc -------233 ----100 ---300

creating an sql query to get the above result?

View 2 Replies View Related

Comparing Rows In The Same Group

Feb 26, 2013

Code:

Room ID Hours StartTime EndTime
GR41908101700
GR42409101300
GR43317102000
LR111108100900
LR112109101000
LR113210101200
LR114112101300
LR115313101600
LR116216101800
LR117118101900
LR118119102000
LR119320102300

As you can see each room is assigned and id and once the room changes the id starts from 1 again. I don't want the 2nd row to appear because 9am - 1pm is in 8am - 5pm. How do I remove this row?

View 3 Replies View Related

How To Group Certain Rows And Get The Data.

Jul 2, 2007

Hi Folks,

I am stuck in forming a query.

My age wise employee count sample data (department wise) is as shown below.

Sample Data
-----------
Department [>55] [50-55] [<50] [<40] [<30]
---------- ----- ------- ----- ----- -----
Marketing 0 1 5 10 20
Op's Support 0 3 6 5 25
Op's Tech 0 0 0 3 10
Product Tech 0 0 2 4 12
Product Support 0 0 1 3 7

I would require the data (sum of employee count age wise) to be categorized at a boarder level. Each category comprising of one or more departments.

Operations [Op's Support + Op's Tech], Product [Product Tech + Product Support], Others [Marketing]
The expected result would be.

Category [>55] [50-55] [<50] [<40] [<30] [Total]
--------- ----- ------- ----- ----- ----- -------
Operations 0 3 6 8 35 52
Product 0 0 3 7 19 29
Others 0 1 5 10 20 36

Thanking you in anticipation.

Jabez.

View 4 Replies View Related

UPDATE TOP Row For A Group Of Rows?

Jan 6, 2008

My first post on the forum, I wish I had found this place sooner, looks to be full of good advice and knowledgeable posters.

I have tried searching and looking at the FAQ's but couldn't find an answer. So here goes, all help greatly appreciated.

SQL Server 2005

Table (many columns left out for simplicity)

ID - unique.
HdrID - key to the header record.
PTtimestamp - date, date/time the row was written to DB.
PType - integer, representing various states of the row.
etc
etc
etc (another 15 columns of data)....

Typically there is approximately 250 records per HdrID.

I am trying to do an SQL UPDATE without using my usual solution of writing vb code!

I want to update the PType of latest row (as per timestamp) for each HdrID to -9999.

ie;

ID HdrID PTimestamp
001 001 01/01/2008 09:00:00
002 001 01/01/2008 09:10:00
003 001 01/01/2008 09:20:00
004 002 01/01/2008 09:00:00
005 002 01/01/2008 09:10:00
006 002 01/01/2008 09:20:00

In the above example I want to update the PType field = -9999 for record ID=003 & ID=006. In reality the table has tens of thousands of rows.

Jake

View 6 Replies View Related

Count Rows In Group

May 1, 2007

Hello.

I built a report with one field as a group.
I want to count the number of rows in each field so I can add it to the group field or somw where in the report.

How can I count how many rows do I have in each group?

Thanks.

View 1 Replies View Related

Group By - Count Returns No Rows

Jun 12, 2007

Hi everyone:
I guess this should be a simple question but have not been able to find the answer, does anyone know how to make a SQL Sentence to return at least one row when counting?
SELECT COUNT(Id_Field), Field2  FROM Table1 WHERE Code_Field = 1 GROUP BY Field2
This will return 0 rows when the where criteria is not matched by any record on the Table1, but I would like to have one row counting 0 rows, in stead it returns no rows at all.
Thanks for any help.

View 4 Replies View Related

Counting Total Rows When Using GROUP BY

Oct 20, 2007

hi,
i have a stored procedure SELECT UserName AS Visitor, COUNT(VisitID) AS TotalVisit
FROM UserVisits
WHERE (ProductID = @ProductID) AND (AnonimIP IS NULL)
GROUP BY UserName
UNION
SELECT AnonimIP AS Visitor, COUNT(VisitID) AS TotalVisit
FROM UserVisits AS UserVisits_1
WHERE (ProductID = @ProductID) AND (UserName IS NULL)
GROUP BY AnonimIP
this will return something like:
zuperboy90 - 4 visits
ANONIMOUS - 6 visits
85.104.103 - 2 visits etc
how can i count the rows returned in both selections (4+6+2 = 12) ?
thank you

View 9 Replies View Related

MS SQL Query : Group By Returing Too Many Rows.

May 30, 2004

Hi,

I have posted this in the Experts Forum so I have put replies to questions in here as well (Hence the long post!!). Hope someone here can help!!

I am having trouble with a T-SQL query. I have three tables in a join and I need to limit the results returned by query using a group by.

All of the fields in the result group are the same except for two fields, a date field and a varchar field. What I want to achieve is to return the row that has the latest date, and I need the varchar field as that is the information I am after. The problem is if I include the varchar field in the group by, it is returned as a separate group, if I do not included it in the group by, Server: Msg 8120 (aggregate fn / group by) error occurs!

Any ideas how to get around this?

Sample code and results below:

Query:

select distinct s.id, [active-from], code, s.desc, [scheme], [market-id], [market-id]+'CODE'
from [coded] c join sec s on s.id = c.id
join [mkt-security] m on m.id = s.id
where ([market-id] = [scheme] or [market-id]+'CODE' = [scheme])
Group by s.id, [active-from], code, s.desc, [scheme], [market-id], [market-id]+'CODE'


Sample results:

id : [active-from] : code : desc : [scheme] : [market-id]
--------------------------------------------------------------------------------------------------------------------------
10449 :1993-07-21 : ADV : PXX Group Limited : ABCD : ABCD : ABCDCODE
10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10469 :1986-10-24 : AQL : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
10469 :2000-10-02 : AQL : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
10469 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE

The result I want to achieve is to only return these two rows:

10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10469 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE: DEFG : DEFG CODE

i.e. those with the latest [active-from] date, but I must have the corresponding code value.

I have tried a correlated sub query on the [active-from] field, but it is possible to have different id’s with the same [active-from] date so it did not work.

The primary key for the [coded] table is the combination of [active-from], code and [scheme]. The tables are truncated and then imported back into the SQL database from a Progress database daily, so restructuring the table(s) is not a possibility.


I have rewritten the query to "AND [ACTIVE_FROM] in (SELECT MAX([ACTIVE_FROM]) FROM CODED AND MAX([CODE]) = S.[CODE] AND MAX([SCHEME]) = S.[SCHEME]", (Which I think is the T-SQL for you query), but it returned the error "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."

Also I think the query need to use a Group by as the results I need returned are those with the latest [ACTIVE_FROM] date WITHIN the group of [ID]and [MARKET-ID]. I can't think of any conditions where I can filter the results in the where clause.

The [ ] in the T-SQL is to identify table names as some of the table names (from the Progress db) use characters (the "-") that are unsupported for table names in T-SQL.

I am using the group by as I want to return specific groups of results ( [ID] and [MARKET-ID] ), that have the latest active-from date. I have made a change to that posted before to illustrate:

All results:

id : [active-from] : code : desc : [scheme] : [market-id] : [market-id]+’CODE’

10449 :1993-07-21 : ADV : PXX Group Limited : ABCD : ABCD : ABCDCODE
10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10769 :1986-10-24 : AQL : CO Ordinary Shares : WXYZ CODE : WXYZ : WXYZ CODE
10769 :2000-10-02 : AQL : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
10769 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE

The result I want to achieve is to only return these three rows:

10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10769 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE: DEFG : DEFG CODE
10769 :1986-10-24 : AQL : CO Ordinary Shares : WXYZ CODE : WXYZ : WXYZ CODE


The reason the last row needs to be returned is that it has a different market id to the other row, despite its active-from date being earlier.

I have tried the self join earlier, but it would then only return 1 of the rows that have an id of 10769!!

Hope this makes sense!!

Cheers,
Guytz

View 2 Replies View Related

Table Group By Rows Then Columns

Oct 31, 2007

I have a table, Table1 with 3 columns as follows: colItemKey, colGrouping1, colGrouping2.
colItemKey is the primary key. Say colGrouping1 has 4 different types: Grp1A, Grp1B, Grp1C and Grp1D and colGrouping2 has 5 as follows: Grp2A, Grp2B, Grp2C, Grp2D and Grp2E. How do I setup my select so that the result set is as follows:

Grp2A Grp2B Grp2C Grp2D Grp2E

Grp1A nnn nnn nnn nnn nnn
Grp1B nnn nnn nnn nnn nnn
Grp1C nnn nnn nnn nnn nnn
Grp1D nnn nnn nnn nnn nnn

View 2 Replies View Related

How To Select Last Rows In Group By Clause

Nov 7, 2008

I have a table which stores datewise Transactions of different items. Fields and sample data is

RecID, ItemID, Date, Received, Issued, Stock
1, 5, 11-03-08, 10, 10
2, 5, 11-05-08, 3, 7*
3, 8, 11-15-08, 25, 25
4, 8, 11-16-08, 8, 33
5, 8, 11-18-08, 6, 27*

Now i want to select last row for each item (indicated by *). Is it possible in one single statement.

View 6 Replies View Related

How To Group 2 Table Rows (urgent)

Jun 13, 2007

Hi

How do i group 2 table rows?

I have a report with a table and it has 58 rows, in some of the rows i am displaying the data like this



Roth contribution (Heading)

data for the roth contribution.





So depending on the options checked sometimes the heading Roth contribution is at the end of the page and the data is the next page. so how can i group 2 table rows together.



I have tried clicking on the 2 rows and grouping it, but nothing seems to appear in the report..



any help will be appreciated.



Regards,

Karen



View 3 Replies View Related

Get Specific Rows From A Table (using Max And Group By)

Apr 8, 2008

Hi

I'm no expert at SQL, I can't figure out how to solve that problem.

The following statement:

SELECT TeamMemberId, max(MonthId) as MonthId
FROM Position
INNER JOIN [Month] m
INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = @yearId OR y.Id < @yearId)
ON m.Id = Position.MonthId
GROUP BY TeamMemberId


Returns
TeamMemberId MonthId
1 14
6 20

which is exactly what I want: The greates MonthId for each TeamMember in a specific year.

But I need more data for this postition, like the PositionName.

But the statement:

SELECT TeamMemberId, max(MonthId) as MonthId, PositionName
FROM Position
INNER JOIN [Month] m
INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = 2 OR y.Id < 2)
ON m.Id = Position.MonthId
GROUP BY TeamMemberId, PositionName

Returns
TeamMemberId MonthId PositionName
1 2 ***. d. Geschäftsführung
6 20 ***. d. Geschäftsführung
1 14 CEO
6 16 CEO

The red rows are the ones I wanna get.
How can I achieve that only the max. MonthId rows are returned, even when I need to select more columns?
Any help is much appreciated.


Regards,

Stefan

View 6 Replies View Related

Reporting Services :: Group By Rows And One Tab For One

Jul 14, 2015

Sales Representative: ABC
"CustomerID"Company NameEmail AddressJun 2015Jul 2015
5033397TOPS FOR SHOESsales@topsforshoes.com$654.85$476.01
5034550Goodnightiesmarcia@goodnighties.com$538.80$295.93
5060377United States Lighting Corpjim@uslightingcorp.com$998.71$658.93
5084043mark Bettencourtbetten1@aol.com$70.97$60.07
5084382Arcana Empothecaryjames@arcanaempothecary.com$2,295.97$2,718.05
5098491Tony WisenTony.Wisen@EDMSupplies.com$40.69$94.37
5099747Crest Ridge Saddlerysaddles@crestridgesaddlery.com$733.86$518.63
5103478Ann Aurburndps@nhicwestmi.com$258.62$289.91
5110483Intimidation Clothing, LLCluke@intimidation-clothing.com$145.28$138.42
Total $5,737.75$5,250.32

I have dataset ready having sales rep ID  and other column like customer ID, company name, Email adress and transaction month as shown above how can i make a  row group by sales rep id and each tab (when we export report to excel)   I mean one tab for A sales repID , another tab for B...I did row group  parent group and group by sales repID but not working as expected.

View 3 Replies View Related

Need All Rows Within Each Group To Be Shown On 1 Page

Jan 2, 2007

Hi,

I have data that is grouped by a code number. One of the code numbers have over 600 rows, while other code numbers have around 10 to 20 rows within it.


When I run the report the code number that has over 600 rows gets split over 2 pages while the other code numbers each get their own page.

How do I make it so that when I run the report the code number that has over 600 rows gets all displayed on the 1 page instead of being split over 2?

Thanks

Ben.

View 10 Replies View Related

Hiding Subtotal Rows When There Is Only 1 Row In The Group

Feb 13, 2007

How could one do this? I understand you could use the COUNT() function, but I'm not sure which object's visibility would best support this. All that I've tried (subtotal area, group visibility) do not seem to work.

If you change the visible property on the subtotal textbox that RS adds, it will only 'blank out' the area where the subtotal row would have been - this doesn't achieve the desired effect of saving space.

View 5 Replies View Related

SSRS Adding Group Rows

Mar 6, 2008



Hello Friends,
I have created a report using SSRS and in that report I am using group rows in one of the matrix. When I tried to display the subtotal of that group row by using the SSRS in-built feature the subtotal column is coming at the last of the matrix columns but I want it at the front .

so it will be like this,

First the total should be displayed then the group members value.

Can anyone help me on this issue.

Thanks & Regards
Shivanandan Gupta

View 7 Replies View Related

Transact SQL :: SUM Of Multiple Rows With Where And Group By

Apr 30, 2015

I have a table Transaction that looks something like the following :

TransactionID          

Currency        Credit             Debit
1     USD      500               0
2     Afcu          6000              0

[Code] ....

I write query like this

select SUM(credit)-SUM(Debit)as [Balance] ,Source from Transaction group by Source

And it came like

Balance Source           
1500                        USD
6000                        Afcu
6800                        INR
7000                        Pfc
-200                         AUD

But I also want to add  Afcu , Pfc with USD and  want output like

Balance Source
14500                        USD
6800                          INR
-200                           AUD

View 3 Replies View Related

SQL 2012 :: Group Concat To Make 2 Rows Into 1?

Oct 14, 2014

I have 2 different types of data for the same key. My queries renders these results into 2 rows. I'd like them to be one row.

Example

DOG Name_a Color_a Age_a Name_B Color_B Age_B
Boxer BO Fawn 1
Boxer Maggie Brindle 4
Lab Jackson Yellow 2
Lab Sandie Black 3

The result I want

DOG Name_a Color_a Age_a Name_B Color_B Age_B
Boxer BO Fawn 1 Maggie Brindle 4
Lab Jackson Yellow 2 Sandie Black 3

View 1 Replies View Related

Transact SQL :: Add Group Number To Similar Rows?

Jun 9, 2015

How can I add a group number to the following query?

For example, I want to be able to have all rows that have Category = 'Field Sales' and Division = 'CA BDM' to be given a unique group number (GN):

RN                   ReportDate Category                       Division                       TotalBalance
-------------------- ---------- ------------------------------ ------------------------------ ---------------------
1                    2015-06-08 Field Sales                    CA BDM                         299743154.3912
2                    2015-06-07 Field Sales                    CA BDM                         299765954.0354
3                    2015-06-01 Field Sales                    CA BDM                         297902654.4172
1                    2015-06-08 Key Accounts                   Life Office                    49954981.74
2                    2015-06-07 Key Accounts                   Life Office                    50016989.22
3                    2015-06-01 Key Accounts                   Life Office                    50169967.26
4                    2015-05-31 Key Accounts                   Life Office                    50169918.01

Becomes

GN   RN                   ReportDate Category                       Division                TotalBalance
-------------------------- ---------- ------------------------------ ------------------------------ ---------------------
1      1                    2015-06-08 Field Sales                    CA BDM                     299743154.3912
1      2                    2015-06-07 Field Sales                    CA BDM                     299765954.0354
1      3                    2015-06-01 Field Sales                    CA BDM                     297902654.4172
2      1                    2015-06-08 Key Accounts                   Life Office                    49954981.74
2      2                    2015-06-07 Key Accounts                   Life Office                    50016989.22
2      3                    2015-06-01 Key Accounts                   Life Office                    50169967.26
2      4                    2015-05-31 Key Accounts                   Life Office                    50169918.01

i.e. each combination of Category+Division results in a new GN.

The query is:

selectROW_NUMBER() over (partition by Category, Division order by ReportDate desc) 'RN'
, ReportDate
, Category
, Division
, sum(BalanceGBP) as 'TotalBalance'
FROM FlowsAndOpenings
group by ReportDate, Category, Division
order by Category, Division, RN

View 2 Replies View Related

Transact SQL :: Getting Random Rows From Table / Two From Each Group

Sep 2, 2015

I am using Sql Server 2008 R2.I have a existing query that basically says

Select Top 50 Subscriber_ID,  Member_Name, Group_ID
from my_table
order by rand(checksum(newid()))

However the client now wants to have at least two from each group_id. There are 17 different groups.  When I run this as is I get about six of the 17 groups in the results.  How can I change this to get at least two results from each group_id?

View 6 Replies View Related

How To Get Result From Query By Group Of (n) Rows Like LIMIT In MYSql

Sep 23, 1999

How to get result from Query by group of (n) rows ?

- Is cursors the better way (and the only) ?

-Is there something as ROWNUM in Oracle or LIMIT in MySql ?

Example plz !

Thanks.

Alex

View 3 Replies View Related

SQL Server 2012 :: How To Pivot Column To Rows Within A Group

Dec 18, 2014

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

View 3 Replies View Related

Combining Results Of Multiple Rows Based On Group?

Jul 17, 2013

I have a table of attributes set up as follows:

ID, Value, Group
1, Football, Sports
1, Baseball, Sports
1, Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
etc.

I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:

ID, Combined Values, Group
1, Football Baseball Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks

View 5 Replies View Related

SQL Statement: Group By And Where Clause Do Not Display Null Rows

Jun 17, 2007


The following is a simplified version of my SQL statement. I am attempting to do a simple count(*) with two groupings and a where clause. This is Select command for a GridView. However, I am unable to display zeros. The rows are completely missing and I would greatly appreciate someone's help. I have already tried Group By All, but that, unfortunately, does not work. Here is my SQL statement:

"SELECT [GENDER], [RACE], COUNT(*) FROM [TABLE] WHERE ([COLUMNNAME] ='SOMETHING') GROUP BY [GENDER], [RACE]"



Thanks for the help in advance!

View 1 Replies View Related

Display A Group In A Report That Has No Data Rows In The Result Set

Dec 18, 2007

Hello

I have a report that retrieves its data from Analysis Services. The data includes a count and dollar value of projects against their current status: It looks something similar to



(group1) status1 10 $200,000

(detail) p1 1 $5,000

p2 1 $10,000

.

.

p10 1 $20,000



(group1) status3 5 $90,000

(detail) .

.



(group1) status4 15 $150,000

(detail) .

.



In the report I hide the detail rows. I have a fixed/known number of statuses (in this case 4) and need to show all 4 in the report. eg



(group1) status1 10 $200,000

(detail) p1 1 $5,000

p2 1 $10,000

.

.

p10 1 $20,000



(group1)status2 0 $0



(group1) status3 5 $90,000

(detail) .

.



(group1) status4 15 $150,000

(detail) .

.

ie in this case I need to show status 2 (that doesn't exist in the data set) with zero totals.



Does anyone know if this is possible to get SSRS to display each of the status groups (in a known fixed list) and then match them to the records in the dataset.

As an alternative, if I were using SQL Server I could add rows to the dataset using a union statement. Is there similar functionality using mdx? My mdx skills are very basic.



Thanks


Stewart

View 1 Replies View Related

TSQL - Avoid Duplicated Rows - Using Distinct / Group By

Sep 3, 2007

Hi guys,
need some help here please...

The code below shows 4 rows.
The first two rows are almost identical, but the two of them exists in the same table as different rows.
Row number 1 is also related to Row number 3 and Row number 2 is also related to Row number 4
The problem is that I have to use only one of then (Rows number 1 or 2) togheter with row 3 & 4.

I thought using GROUP BY RECEIPTJURNALMATCH.JURNALTRANSID, but getting error.
Thanks in advance,
Aldo.




Code Snippet
SELECT
RECEIPTJURNALMATCH.JURNALTRANSID AS 'R.JURNALTRANSID',
RECEIPTJURNALMATCH.MATCHNUM AS 'R.MATCHNUM',
JURNALTRANSMOVES.ACCOUNTKEY AS 'J.ACCOUNTKEY',
JURNALTRANSMOVES.SUF AS 'J.TOTAL',
STOCK.REMARKS AS 'S.REMARKS'

FROM
RECEIPTJURNALMATCH
INNER JOIN JURNALTRANSMOVES ON RECEIPTJURNALMATCH.JURNALTRANSID = JURNALTRANSMOVES.ID
LEFT OUTER JOIN STOCK ON RECEIPTJURNALMATCH.STOCKID = STOCK.ID

WHERE
JURNALTRANSMOVES.ACCOUNTKEY IN ('123456')


Below the results:

R.JURNALTRANSID R.MATCHNUM J.ACCOUNTKEY J.TOTAL S.REMARKS
89634 16702 123456 1155 ×¢×—: ;5752
89634 16703 123456 1155 ×¢×—: ;5752
89637 16702 123456 400 NULL
89639 16703 123456 155 NULL





View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved