?
Solved

TSQL Count

Posted on 2010-01-05
11
Medium Priority
?
313 Views
Last Modified: 2012-05-08
i have the following query but i need to include the number of phones linked to the Number
SELECT   
	TblPhone.PhoneSerialNumber, 
	TblJobDetails.Number
FROM         
	TblPhone 
		INNER JOIN
			TblPhoneDetails ON TblPhone.PhoneID = TblPhoneDetails.PhoneFK 
		INNER JOIN
            TblJobDetails ON TblPhoneDetails.JobDetailsFK = TblJobDetails.JobDetailsID 
		INNER JOIN
			TblPhoneInstallation ON TblPhoneDetails.PhoneDetailsID = TblPhoneInstallation.PhoneDetailsFK

ORDER BY TblJobDetails.Number

Open in new window

0
Comment
Question by:aneilg
11 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 26181217
something like this


SELECT  
      MIN(TblPhone.PhoneSerialNumber)PhoneSerialNumber,
      TblJobDetails.Number,
      COUNT(TblJobDetails.Number ) cnt
FROM        
      TblPhone
            INNER JOIN
                  TblPhoneDetails ON TblPhone.PhoneID = TblPhoneDetails.PhoneFK
            INNER JOIN
            TblJobDetails ON TblPhoneDetails.JobDetailsFK = TblJobDetails.JobDetailsID
            INNER JOIN
                  TblPhoneInstallation ON TblPhoneDetails.PhoneDetailsID = TblPhoneInstallation.PhoneDetailsFK
GROUP BY TblJobDetails.Number
ORDER BY TblJobDetails.Number
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26181253
This will give you a record count of phones per number

SELECT  
		TblJobDetails.Number,
        count(TblPhone.PhoneSerialNumber) as NoOfPhoneSerials        
FROM          
        TblPhone  
                INNER JOIN 
                        TblPhoneDetails ON TblPhone.PhoneID = TblPhoneDetails.PhoneFK  
                INNER JOIN 
            TblJobDetails ON TblPhoneDetails.JobDetailsFK = TblJobDetails.JobDetailsID  
                INNER JOIN 
                        TblPhoneInstallation ON TblPhoneDetails.PhoneDetailsID = TblPhoneInstallation.PhoneDetailsFK 
 
ORDER BY TblJobDetails.Number

Open in new window

0
 
LVL 4

Assisted Solution

by:fsouzabrasil
fsouzabrasil earned 500 total points
ID: 26181271
SELECT  
      MIN(TblPhone.PhoneSerialNumber) as PhoneSerialNumber,
      TblJobDetails.Number,
      COUNT(TblJobDetails.Number ) as cnt
FROM        
      TblPhone
            INNER JOIN
                  TblPhoneDetails ON TblPhone.PhoneID = TblPhoneDetails.PhoneFK
            INNER JOIN
            TblJobDetails ON TblPhoneDetails.JobDetailsFK = TblJobDetails.JobDetailsID
            INNER JOIN
                  TblPhoneInstallation ON TblPhoneDetails.PhoneDetailsID = TblPhoneInstallation.PhoneDetailsFK
GROUP BY TblJobDetails.Number
ORDER BY TblJobDetails.Number
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:shru_0409
ID: 26181300
SELECT   tblphone.phoneserialnumber, tbljobdetails.Number,
             count(*) over ( partition by tbljobdetails.Number) cnt
    FROM tblphone INNER JOIN tblphonedetails ON tblphone.phoneid =  tblphonedetails.phonefk
         INNER JOIN tbljobdetails ON tblphonedetails.jobdetailsfk =   tbljobdetails.jobdetailsid
         INNER JOIN tblphoneinstallation ON tblphonedetails.phonedetailsid =  tblphoneinstallation.phonedetailsfk
ORDER BY tbljobdetails.Number

try this
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26181358
I dont think you can include tblphone.phoneserialnumber in the select statement or the group by, if you do you will get number of phones per number per serial.
Also selecting the min(tblphone.phoneserialnumber) will only bring back 1 serial not all, and this also assums that phoneserialnumber is numeric
0
 

Author Comment

by:aneilg
ID: 26182093
Thanks for that guys, i'll give it a go.

 ii agree with 26181358, i need all the numbers not just one.
I was also not sure about by order by as well.


0
 

Author Comment

by:aneilg
ID: 26182181

Can someone explain why I need a Group By and an ORDER BY.

Because i also get an error and not sure why.

ie it is not contained in an aggregate function ect.
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26182221
U don't need the order by. The group by groups ur data together so you can count / sum etc by a selection or group of data. Or group by x and sum y will tell you how many rows there are by field c
0
 

Author Comment

by:aneilg
ID: 26182296
Nice one guys.

Thats what was getting me, it's good to get an explaination, helps to understand where i am going wrong.

Thanks again.
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26182306
No. Please don't forget to mark a solution
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26184514
Hi aneilg
Please could you close this question.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 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