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


Top 5 Contributors of the Month
david stephan
Post New Web Links

Linq to SQL group by category and count each subcategory

Posted By:      Posted Date: September 29, 2010    Points: 0   Category :ASP.Net
 

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                     


View Complete Post


More Related Resource Links

Need Help in LINQ query for group By with chunks of record

  

I am assigning and unique id [strShipperIdSequence] on my List on bases of some properties which are grouped together uniquely.
Now what i needed is that my group should be further break down to some maximum amount of chunks.[Let say 10]
that mean's even i am having Same value in 12 records i should get 2 groups[I of 10 items and other of 2 items]

var uniqueGroups = objMdbContentInfoList.GroupBy(p => new
{
p.CON_ENTRY_POINT,
p.APPTType,
p.CON_CONTAINER_ISSUE_DATE
}).Select(g => g.First()).ToList();
foreach (var objUnique in uniqueGroups)
{
string strShipperIdSequence = APIGlobalMethods.GetShipperRequestID();
foreach (MdbContentInfo obj in objMdbContentInfoList.FindAll(h => (h.CON_ENTRY_POINT == objUnique.CON_ENTRY_POINT &&
h.APPTType == objUnique.APPTType &&
h.CON_CONTAINER_ISSUE_DATE == objUnique.CON_CONTAINER_ISSUE_DATE)))
{
obj.Shipper

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"  

Get Distinct value from a Group-Join syntax Linq

  
Hello,I have this group-join syntax but I couldn't get the distinct value from the relational tables below.Table_1key_1 t1_value1     Food2     Sports3     Leisure 4     Trip5     ZooTable_2key_2   key_1  t2_boolean15        1          True16        1          True17        1          True18        2          True19        2          True20        2          Falsedesire result:FoodSports            Dim c = (From t1 In dbContext_Table_1 _                                     Group Join GroupTable In _db.Table_2 On GroupTable.key_1 Equals t1.key_1 Into Group 

Create Collection Template Category/group

  
Is there a way in WSS 3.0 to create a custom group/category to hold custom templates?  I created a project template and would like to create a group for my company as opposed to having the template go into the "Custom" category.  I have searched google over an no posts are available on this topic and stsadm does not have a command for creating a group.

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

  

How to obtain the resultset using one select statement?

 

Table 1

CustId

OrderId

A

1000

A

1001

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


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.

Object Must Implement IConvertible error in LINQ to Entities with group by

  
1 down vote favorite
1

Hi. I have an ASP.NET MVC application in which i am using entity model for connecting to MySQl database

Here I am joining 2 tables and then applying group by on one of the fields. Then fetching the result from GroupBy result. Everything is working fine on development machine but getting  error:

"Object Must Implement IConvertible"   when executing on the stagging server.

Can you please help me out why such error is occurring. I have taken the dump of database form staging server and then used that on development machine then also working fine. But when I placed the dump of development machine database on staging then getting the same exception.

So where is problem i am not understanding.

The model structure is

student:
studentid
int
course
string
name
string

student_payment
:
id
int
studentid
int
amount
decimal

Here student and st

Reporting Services 2005 - Scatter Report Unable to show category group

  

Hi,

  I am using scatter chart to create some sort like

 

Quantity  Time  Machine Name

12 2010-09-10 04:21:11         M1

22 2010-09-10 19:21:11  M1

100 2010-09-11 05:21:11  M1

89 2010-09-11 07:21:11  M1

 

66 2010-09-12 07:21:11  M2

18 2010-09-13 07:21:11  M2

 

68  2010-09-12 07:21:11  M3

 

X-axis show me the quantity, Y-axis shows me the time and then machine name group its time and qty under different category group. How to make it.

I have tried to use scatter chart to display but I can only see the X and Y -axis and without category group shown...  


VB.NET Linq - Multiple column Group By using Anonymous types does not work properly

  

I am attemping to do a multiple column group by in linq using VB.  I've followed a code sample i obtained at the following url:

http://msdn.microsoft.com/en-us/vbasic/bb737926.aspx#grpbymult

the sample code is:

GroupBy - Multiple Columns

This sample uses Group By to group products by CategoryID and SupplierID.

Public Sub LinqToSqlGroupBy09()
    Dim categories = From p In db.Products _
                     Group By Key = New With {p.CategoryID, p.SupplierID} Into Group _
                     Select Key, Group

    ObjectDumper.Write(categories, 1)
End Sub



My code result though is not correct, as i am getting duplicates.   The first query is using the data which is stored in a datatable.  The second table pulls the data from a list of objects which i manually built in an effort to get closer to the Example provided above.

Dim listNamesWithSeverity = From myrow As DataRow In dt.Rows _
                

Optimizing Linq Group By (large data set)

  

Hello,

I've noticed that the query below takes a very long time to run... sometimes around 1 minute, when the "results" set exceeds about 30k records. The "results" set is search results based on a users search criteria, and even with 30k+ records, only takes about 1 second execute. The purpose of the query below is to populate a "Top 5 Keywords" ListView of Keyword links that will allow the user to further filter the results set. I'm assuming the the grouping for a 1-m relationship over 30k records is why this is taking so long. Any idea how to improve this query to be < 1 sec, or should I be going about this a different way? Thanks!


            var uniqueKeywords = (
                from result in results
                join kit in db.KeywordInTitles on result.TitleId equals kit.TitleID

group by in linq

  

i have the following

from a in entities.Accounts
join d in entities.SubAccounts
on a.AccountID equals d.Accounts.AccountID
select new AccountReturn{acct = a, subacct = d}

what i need is to get the account and the count of the subaccounts 

 kind of like the following in sql but i need the full account. 

SELECT t1.accountid, t1.name,  COUNT(*)
FROM accounts t1
JOIN dealers t2
ON t1.accountid = t2.accountid
GROUP BY t1.accountid, t1.name
 how do i do this in linq?


 


A very simple question on the LINQ Count function, but I can't figure it out....

  

Hi guys, I think this must be really simple but for some reason I can't get this to work.  I have an arraylist of stock ticker names and the same names can appear multiple times in the list.  I am trying to create a LINQ query to find which ticker names appear a certain number of times in the list. 

The arraylist is like: {Dell, Citi, Appl, Appl, Citi, Msft, Dell, Appl, Dell}

If I want to find which tickers appear 3 times in the list, which is named "stocklist", my current query is:

 

Dim newquery = From ticker In stocklist _

Where ticker.count = 3 _

Select ticker

 

But the query keeps giving me an error when I try to run it.  I'd greatly appreciate if someone could please let me know how to correct this problem.  Thanks in advance!


If or Case in a LINQ SQL code, How to Use Count() Request.QueryString("imageidentity")

  

Hi

If i want to get some diffrent data from a DB
and its by using Request.QueryString("SortBy")

What do u use !?
If The Else or Case !?

What i want is that if SortBy in the Url link is NameUP, then its sorts by A, B C and so on
and if NameDown then the other way.

Im using this LINQ code.

    Public Sub linqdb()

        Dim DBconn As New GetProductListDataContext()
        Dim Q = _
        From p In DBconn.GetTable(Of Product)() _
        Where p.IsEnabled = "1" _
             Order By p.PName Descending _
        Select p
        Dim L = Q.ToList
        MyRep.DataSource = Q
        MyRep.DataBind()
        PValue.Text = L.Count().ToString()

    End Sub


 How do i also get this code to Count the outgoing records !?
I cant get any number from PValue.Text = L.Count().ToString()
from my CB to my .ASPX site with a <asp:Label ....


caml query - group by column and get count of records

  

Hi,

I want to group by on column name and get the count of records returned.

 

 


Same measure group for count and distinct count in SSAS

  

Hello there,

I need to make DistinctCount aggregation of few columns in the same fact table in SSAS. For each DistinctCount it creates a new measure group. I need to put all these under same measure group. Please let me know how to go ahead.

Regards,

Palash


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