.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Sharon Maxwell
Post New Web Links

How to get group count and total count using one select statement?

Posted By:      Posted Date: September 30, 2010    Points: 0   Category :Sql Server
 

How to obtain the resultset using one select statement?

 

Table 1

CustId

OrderId

A

1000

A

1001


View Complete Post


More Related Resource Links

SSAS 2008 Measure group Distinct count

  
Hi all, I have a data of as number of trasactions,DD, SO, BouncedDD, CancelledDD all are in count (number) while adding these measures manullay to a measure group I have selected usage as DistinctCount for one measure and for all the remaining measures as DiscinctCount.While deploying the cube it shows error as "Fact table canot have more than one distinct count"  

SELECT COUNT(*) FROM [Table] from an Oracle database

  
Hi friends, I have problem when retrieving a result from SELECT COUNT(*) FROM [Table] from an Oracle database. When I try to put the result (single row) in a variable I get the following error message. [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowsSource": "Unsupported data type on result set binding RowsSource.". Pls help me Mahe

Problem with SELECT COUNT query and parameters

  
Hello!I have a problem with SELECT COUNT query in ASP.net. I want to create CMS with articles which have categories (which have the option to be deleted). The problem is that I want to get the number of articles within the specified category so if there aren't any articles with the specified category I can proceed with the category deletion.I have the following code:protected void Page_Load(object sender, EventArgs e) { } protected void GridViewKategorije_RowCommand(object sender, GridViewCommandEventArgs e) { if (e.CommandName == "Uredi") { int index = Convert.ToInt32(e.CommandArgument); GridViewRow odabraniRed = GridViewKategorije.Rows[index]; TableCell ClanakID = odabraniRed.Cells[2]; string ID = ClanakID.Text; Response.Redirect("/Portal/Administracija/Kategorija.aspx?idKategorija=" + ID); } else if (e.CommandName == "Obrisi") { int index = Convert.ToInt32(e.CommandArgument); GridViewRow odabraniRed = GridViewKategorije.Rows[index]; TableCell KategorijaID = odabraniRed.Cells[2]; String connString = WebConfigurationManager.ConnectionStrings["CMS"].ToString(); SqlConnection conn = new SqlConnection(connString); conn.Open(); using (SqlC

Select Count(), then For loop?How?

  

Hi...all experts.

I would like to do a program where

1) Need to get a "number" by select count() from a gridview. Then,

2) Mayb using "For loop" to loop until "number".

As example:

1) i get the select count() from as gridview as 5. Then,

2) I got to loop 5 times.

Can someone give me an example of code for my logic?

Thank you for your kind attention and patient.


Scope statement using Tail function has count error

  

My time hierarchy (TimeHier) has a QUADWEEK level with 28 members. When I select the last 13 members with the Tail function, I get the appropriate members:

Tail([Time].[TimeHier].[QUADWEEK].Members, 13))

However, if I use that same function inside a Scope statement, it only applies to the last 12 members. I have to increase the count by 1, to 14 to make it work inside the scope. I'm baffled.

Here's the full code:

CREATE

 

MEMBER CURRENTCUBE.[MEASURES].[YagoMemString] A

Linq to SQL group by category and count each subcategory

  

I am wonder how I can generate a result table from linq to sql as below

Car sales table
Toyota              small
Toyota              passenger
Toyota              small
Honda               small
Honda               small
Honda               passenger
Honda               small

result table
Brand                small            passenger     total
Toyata                2                  1                     

How to get total record count in an OUTPUT Parameter

  

I couldn't get the total record count in a, in my stored proc, in a given scenario. Please help

 

My SP looks like this:

create proc RecordCount

       @CustomerID varchar(10),

       @RecCnt int OUTPUT

as

begin tran

if(CustomerID = 'ABC')

@RecCnt = select count(*) from Customers where CustomerID='ABC'

commit

 

begin tran

if(customerID='XYZ')

@RecCnt = select count(*) from Customers where CustomerID='XYZ' and TranDate > '05/01/2009'

commit

GO

 

-- This is how I called my SP

declare @rc int

exec RecordCount 'XYZ', @RecCnt = @rc OUTPUT

select @rc


SSIS audit column Get date and total count of row

  

HI

I have one task in which i have to transfer data from sql to flat file  also i need one more flat file in which i have Number of Row and Getdate

I use multicast column  and then agreegate to count row  i do not know How to achive getdate in this.

 

 


Select statement with two group level to create addtional field.

  

Here is an example of my question

I have  a column called Department, a column called Employee Name and a column called EmployeeAbsent.

Obviously i can easily use a select satement to get each employee's absent status. However, I would like to generate an additional field called DepartmentAbsent to see if anyone in a particular depart is absent based on the EmployeeAbset field.

how should I write the query?


Stored procedure help (group by, sub queries, count etc...)

  

Hi There,

im writing a stored procedure.... Given a groupID, i want to get all the groups from the groups table and also two extra columns....

the first extra column is UserCount, this goes off to the user's table and pulls out a user count for the group.

the second extra column is GroupCount. This goes away and pulls out all other groups that have a ParentID as the incoming org.


I know the stored procedure I have supplied isn't correct, im suspecting I need to have sub queries and group by's.... but sql isn't my forté.

The reason I need these properties returned this way is so that LinqToSql can create a custom object based on this return.



ALTER PROCEDURE [dbo].[uspGetTreeViewGroups]
	-- Add the parameters for the stored procedure here
	@GroupID int
AS
BEGIN

	SELECT *, (SELECT COUNT(*) FROM tblUser u
				   WHERE u.GroupID = @GroupID
				   AND u.Enabled = 1) as UserCount,
				   (SELECT COUNT(*) FROM tblGroup g
					WHERE g.ParentID = @GroupID) as ChildCount
	FROM tblGroup g
	WHERE (g.ParentID IS NOT NULL)

END



Thanks


IN SSAS 2005, total count is incorrect for a partial month

  

Hi,

IN SSAS 2005, when I navigate Cube->browser and I drag my fields "Day of week" to rows and couple of measures namely "M1" and "M2" to columns

and applied a filter "hierarchydate" in the filter section(eg partial month Jan 10 to Jan31), I get the total count incorrect. I also have the following observations

1) for example "Instead of giving Sunday "Sub total" for that partial month i.e "Jan 10 to Jan31", it is displayed with all the Total Sunday's count for all the years

2) IN Cube-> browser , it is working fine, if I apply the filter in "select dimesion" section instead of applying filter in "drop filter fields section"

Is there any such kind of issue in SSAS 2005?

Please reply me ASAP if you have any information


count number of columns in group

  

hello,

I need to count number of columns in group -

I have a matrix with SUM in the date area.

I have a group column (lats say 'city') and under it my columns (lats say 'street name');

Under each 'street name' I have cell with SUM function.

The problem is:

When The main group (city) is not expand I get the sum of all cities and what I want to get is an average .

So what I thought of was to divide that number in the number of streets under each city group.

How can I get that number?

Thanks.


Group by with Count(*)

  

USERS TABLE COLUMNS

================

USERID - NVARCHAR

REGISTRATIONDATE - DATETIME

EXPIRYDATE - NVARCHAR

STATUS - NVARCHAR

FROMID - NVARCHAR

ORGANISATIONID - NVARCHAR

========================

ORGANISATION TABLE COLUMNS

=======================

ORGANISATIONID - NVARCHAR

NAME - NVARCHAR

COUNTRY - NVARCHAR

 

QUERY:-   TO GET NO.OF REGISTRATIONS BETWEEN PARTICULAR DATE (WHERE Start and End MONTH, YEAR ACCEPTED FROM INTERFACE) BELOW stored procedure is used. Just now it gets all records where the RegistrationDate IS between Start and End Dates AND Status='Expired' not considering ExpiryDate. So it is not getting correct expired users with this query.

As per the requirement it should

1. Count No. of Registered users through 'Microsoft' --working fine just now.

2. Count No. of Registered users through 'Java' - working okay.

3. Count No. of Expired users BETWEEN START MM/YYYY AND END MM/YYYY -- Need Help.

i want to count total packet sent and received while pinging so i how i can do this ??

  
 lblStatus.Text = ""
            Dim ping As Ping = New Ping()
            Dim pingreply As PingReply = ping.Send(txtHost.Text)
            Label2.Text = "Address : " & pingreply.Address.ToString() & Constants.vbCr
            Label3.Text = "Roundtrip Time : " & pingreply.RoundtripTime & Constants.vbCr
            Label4.Text = "TTL (Time To Live): " & pingreply.Options.Ttl & Constants.vbCr
            Label5.Text = "Buffer Size : " & pingreply.Buffer.Length.ToString() & Constants.vbCr
            Label6.Text = "Packet-sent : " & pingreply

Add a cumulative total column to a select statement

  

I have a temp table with acct and balance; I would like to do a select statement that would add an additional column that has a cumulative total.

SELECT

 

 acct, Balance, cumBal

FROM #Temp1

ORDER

 

Select Distinct with Count

  

This is something I can easily do in SQL, but I'm pretty new to LINQ and haven't been able to find an answer online.

I need a LINQ query that returns two values; the first a distinct value and the second the count of the first value. This is filtered by a parameter. Here is my table:

ID

City

State

1

Portsmouth

Hampshire

2

Portsmouth

Hampshire

3

Southampton

Hampshire

4

Brighton

East Sussex

In SQL I use the following:

SELECT DISTINCT City, COUNT(*) AS Expr1
FROM My_Table

WHERE State = @State

If @State is equal to Hampshire I get the following result

Portsmouth

2

Southampton

1

I have managed to create a query that brings back a distinct City;

var recordset = (from s in MyTable where s.State == state select new { s.City,&nb

Record Count on MERGE Statement on Insert,Update,Delete

  

HI All,

How can I get the numbers of records affected in the Merge statement, INSERT,UPDATE,DELETE  separately and store it in a variable so I can get it in the application side? 

Thanks.

 


khrizz tell
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend