CODEWITHSUNDEEP
asp.netsql-serversql-server-2005linq-to-sql
Sreedhar Danturthi
Sreedhar Danturthi

Reputation: 7571

Count the number of elements in sql server table

I have 2 tables which looks like this:

ARTICLES TABLE:

and the output should look like this:

enter image description here

How can I accomplish this using both sql query (i'm using sql server 2005) and using linq to sql query ?

BTW i'm using sql server 2005, asp.net with c# in Visual studio 2008.

Please help me

Thanks in anticipation

Upvotes: 1

Views: 1108

Answers (3)

Chris Taylor
Chris Taylor

Reputation: 53709

Update: Added Linq experssion that can be used if you require an OUTER join.

INNER JOIN

For an inner join ie. only get back the articles that have been bought at least once, you can use the following.

LINQ 2 SQL

from a in Articles
join c in CustomersRecords on 
     a.Article_Name equals c.Article_Name 
group a by new {a.SNo, a.Article_Name} into g
select new 
       {
         SNo = g.Key.SNo, 
         Article_Name = g.Key.Article_Name, 
         Total_Items_Bought = g.Count()
       }

The above translates to the following SQL

SELECT COUNT(*) AS [Total_Items_Bought], [t0].[SNo], [t0].[Article_Name]
FROM [Articles] AS [t0]
INNER JOIN [CustomersRecord] AS [t1] ON [t0].[Article_Name] = [t1].[Article_Name]
GROUP BY [t0].[SNo], [t0].[Article_Name]

Which when cleaned-up a little gives you

SELECT a.SNo, 
       a.Article_Name, 
       COUNT(*) AS Total_Items_Bought
  FROM Articles AS a
 INNER JOIN CustomersRecord AS c ON a.Article_Name = c.Article_Name
 GROUP BY a.SNo, a.Article_Name

LEFT OUTER JOIN

For a left outer join ie. get back all articles event those that have never been bought, you can use the following.

LINQ 2 SQL

from a in Articles
join c in CustomersRecords on 
     a.Article_Name equals c.Article_Name into apc
select new 
       {
         SNo = a.SNo, 
         Article_Name = a.Article_Name, 
         Total_Items_Bought = apc.Count()
       }

This translates to the following SQL

 SELECT [t0].[SNo], [t0].[Article_Name], (
    SELECT COUNT(*)
    FROM [CustomersRecord] AS [t1]
    WHERE [t0].[Article_Name] = [t1].[Article_Name]
    ) AS [Total_Items_Bought]
 FROM [Articles] AS [t0]

Upvotes: 2

Govind Malviya
Govind Malviya

Reputation: 13753

Use this for SQL

SELECT 
   SNO,Article_Name, 
   (SELECT COUNT(*) FROM CustomersRecord AS cr 
    WHERE cr.Article_Name = Article_Name) AS Total_Items_Bought 
FROM ARTICLES

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

select 
  A.SNo,
  A.Article_Name,
  count(C.Article_Name) as Total_Items_Bought
from Articles as A
  left outer join CustomersRecord as C
    on A.Article_Name = C.Article_Name
group by A.SNo, A.Article_Name    
order by A.SNo

Upvotes: 2

Related Questions