Count of related records?

Hi,

I was wondering if it was possible to build a query that will include a
column that will provide a count related records from another table.
Although there is a way to achieve this through programming in the
front end, I would like to know if it possible to achieve the same
thing through a SQL statement alone.

For example, say you have two related tables, Invoices and
InvoiceItems. InvoiceID is the primary key of Invoices.

Invoices table

InvoiceID PO_Num CompanyID
----------
1 37989 3
2 87302 4
3 78942 3

InvoiceItems table

ItemID InvoiceID PartNo Qty
------------
1 1 ABA 3
2 1 ASLKDJ 2
3 1 9LF 8
4 2 IEPOW 18
5 2 EIWPD 3
6 2 DSSIO 1
7 2 EIWP 5
8 2 DC93 4
9 3 85LS0 8

Then a query that has the Invoices table plus a count of InvoiceItems
for each InvoiceID would generate this:

InvoiceID PO_Num CompanyID ItemCount
---------------
1 37989 3 3
2 87302 4 5
3 78942 3 1

Does anyone have any ideas how this would be done?

Thank you.SELECT I.invoiceid, I.po_num, I.companyid,
COALECSE(T.cnt,0) AS itemcount
FROM Invoices AS I
LEFT JOIN
(SELECT invoiceid, COUNT(*) AS cnt
FROM InvoiceItems
GROUP BY invoiceid) AS T
ON I.invoiceid = T.invoiceid

--
David Portas
SQL Server MVP
--

count function

hi

I have a question related to XQUERY in SQL Server . is SQL Server 2005 supports Count function.

i want to make a query in which i want to search for some keyword in all the nodes. If there is some match then count the number of times that word appeared in XML.

Can anybody give me some clue about how to make this query .

thanks in advance

Regards

Ambi

XQuery includes the count() function to count sets of nodes. Here is the documentation on this function containing an example:

http://msdn2.microsoft.com/en-us/library/ms189963.aspx

If you want to query over all the nodes in the query, then the "//" ("descendents") operator will allow you to apply a filter to each node, then you can pass that nodeset to the count() function. Here is the documentation on all the path steps including //

http://msdn2.microsoft.com/en-us/library/ms190451.aspx

sql

count function

hi

I have a question related to XQUERY in SQL Server . is SQL Server 2005 supports Count function.

i want to make a query in which i want to search for some keyword in all the nodes. If there is some match then count the number of times that word appeared in XML.

Can anybody give me some clue about how to make this query .

thanks in advance

Regards

Ambi

XQuery includes the count() function to count sets of nodes. Here is the documentation on this function containing an example:

http://msdn2.microsoft.com/en-us/library/ms189963.aspx

If you want to query over all the nodes in the query, then the "//" ("descendents") operator will allow you to apply a filter to each node, then you can pass that nodeset to the count() function. Here is the documentation on all the path steps including //

http://msdn2.microsoft.com/en-us/library/ms190451.aspx

couldn't see some of the database in the object explorer window of Management Studio

hey guys,
I have one question. But it is not related with the Reporting Service. I got my computer set up with SQL Server 2005 version yesterday. When I connect to the database engine, I can see only system databases(master,model,msdb and tempdb) under the system database folder. I couldn't see other databases(like pubs,northwind) in the object explorer window of Management Studio. Do you have any idea how to solve this difficulty?

Thanks.

Amde

Hi,

no problem there. These databases aren′t installed by default. There are scripts to build up the database from scratch:

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

Or you use the Adventureworks sample database:

http://www.microsoft.com/downloads/details.aspx?FamilyID=e719ecf7-9f46-4312-af89-6ad8702e4e6e&DisplayLang=en

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks a lot. I will work on that.

Amde

Couldn't see some databases in the object explorer window of Mangement Studio

hey guys,
I have one question. But it is not related with the Reporting Service. I got my computer set up with SQL Server 2005 version yesterday. When I connect to the database engine, I can see only system databases(master,model,msdb and tempdb) under the system database folder. I couldn't see other databases(like pubs,northwind) in the object explorer window of Management Studio. Do you have any idea how to solve this difficulty?

Thanks.

Amde

Pubs and northwind are no longer installed by default.|||

How about the other databases like Adventurework db? If you have some idea how to configure the database engine, please let me know.

I appreciate your response.

Amde

|||

Adventureworks is not installed by default either. Your questions are more appropriately suited for the "SQL Server Tools General" forum. http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1.

An easy test is to simply create a new database, refresh the object explorer, and you should see it there.

I am not certain what the best way to install the sample databases is post-installation. You should ask that question either in the tools forum or the setup forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=95&SiteID=1).

|||

Thank you for your advise. I will post this question on the appropriate form discussion board.

|||

Hi, Amde --

This content will be available in the next version of BOL --

Before you can use SQL Server 2005 samples, you must install them. Installing the samples is a two-step process. Samples are not installed by default and SQL Server Setup does not run the sample installation program for you. First, when running setup, on the Features Component page, select Samples. This will copy the samples installation program to your computer during setup. Second, to run the samples installation program, point to the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Documentation and Samples, point to Samples, and click Microsoft SQL Server 2005 Samples. To get the most recent samples, uninstall your current samples, and download and install the latest SQL Server 2005 samples from the Microsoft download Web site. To uninstall the current version of samples, point to the Start menu, point to Control Panel, and click Add or Remove Programs. Click Microsoft SQL Server 2005 Samples, and then click Remove. To find the most current samples, search http://www.microsoft.com/downloads/ for “SQL Server 2005 Samples and Sample Databases”. Choose the edition that matches your Reporting Services installation.

-- Mary

Couldn't see some databases in the object explorer window of Mangement Studio

hey guys,
I have one question. But it is not related with the Reporting Service. I got my computer set up with SQL Server 2005 version yesterday. When I connect to the database engine, I can see only system databases(master,model,msdb and tempdb) under the system database folder. I couldn't see other databases(like pubs,northwind) in the object explorer window of Management Studio. Do you have any idea how to solve this difficulty?

Thanks.

Amde

Pubs and northwind are no longer installed by default.|||

How about the other databases like Adventurework db? If you have some idea how to configure the database engine, please let me know.

I appreciate your response.

Amde

|||

Adventureworks is not installed by default either. Your questions are more appropriately suited for the "SQL Server Tools General" forum. http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1.

An easy test is to simply create a new database, refresh the object explorer, and you should see it there.

I am not certain what the best way to install the sample databases is post-installation. You should ask that question either in the tools forum or the setup forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=95&SiteID=1).

|||

Thank you for your advise. I will post this question on the appropriate form discussion board.

|||

Hi, Amde --

This content will be available in the next version of BOL --

Before you can use SQL Server 2005 samples, you must install them. Installing the samples is a two-step process. Samples are not installed by default and SQL Server Setup does not run the sample installation program for you. First, when running setup, on the Features Component page, select Samples. This will copy the samples installation program to your computer during setup. Second, to run the samples installation program, point to the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Documentation and Samples, point to Samples, and click Microsoft SQL Server 2005 Samples. To get the most recent samples, uninstall your current samples, and download and install the latest SQL Server 2005 samples from the Microsoft download Web site. To uninstall the current version of samples, point to the Start menu, point to Control Panel, and click Add or Remove Programs. Click Microsoft SQL Server 2005 Samples, and then click Remove. To find the most current samples, search http://www.microsoft.com/downloads/ for “SQL Server 2005 Samples and Sample Databases”. Choose the edition that matches your Reporting Services installation.

-- Mary

sql

Could not connect server from client machine

Hi all

I m a new member to this forum. Im a hardware engineer who rectify aome software related issues. Now our employees having problem with connecting their XP prof based clients to 2003 server from SQL query analyzer. I tried refresh sql server manager in server and tried restarting the server machine many times. sometimes it the client machine connects and disconnects immediately. i aslo tried changing the poll interval , but no use.

waiting for a speedy solutin... i know this is as easy query.

Hi mvramsubbu,

A warm welcome to forum

You need to digg more to get the Exact erro message refer Windows Event Viewer and SQL Server's Error Log for error and post them here so that you can get a good answer. unless you post the error message we can not help you.

Hemantgiri S. Goswami