Solved

Counting the product delivery

Posted on 2011-03-10
2
220 Views
Last Modified: 2012-05-11
Hi, I have two tables namely tblProduct and tblDelivery with the following definition:

tblProduct - pid,pdesc
tblDelivery - pid,date,time,location

where the keys are bold.

Now I want to construct a sql statement to display pid,pdesc,totalNumberOfDeliveryBasedOnDate sorted by pid, date

Please advice me on how to construct this sql statement.
0
Comment
Question by:bpyeo
2 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 500 total points
ID: 35093611
SELECT P.pid, P.pdesc, total AS totalNumberOfDelivery
FROM tblProduct P
   INNER JOIN (      SELECT X.pid, X.[date], COUNT(*) as total
                        FROM tblDelivery X
                        GROUP BY X.pid, X.[date])  D ON P.pid = D.pid

ORDER BY P.pid, D.[date]
0
 
LVL 5

Expert Comment

by:mayankagarwal
ID: 35093641
select a.pid, a.desc, count(b.pid), b.date from tblProduct a, tblDelivery  b where a.pid = b.pid group by b.date
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now