Count Function

Question

Count Function

jennigirl

Old Hand

Points: 372
More actions
January 18, 2012 at 8:07 am

#255078

I was able to get the query results I need, but I am looking to now present the results as the total count of trouble tickets assigned to each person. I can get a total count of rows returned, but I am trying to get a count of tickets assigned to each person. I don't work a lot with aggregate functions so this one is stumping me.
SELECT
DISTINCT TroubleTicket.TicketNumber,
Asset.Person
FROM
Asset
RIGHT JOIN TroubleTicket
ON Asset.Id = TroubleTicket.AssetId
RIGHT JOIN Item
ON (Asset.Person = Item.Person and Asset.CompanyId = Item.CompanyId)
WHERE
(TroubleTicket.UT02 IS NOT NULL AND TroubleTicket.UT03 IS NOT NULL)
AND Item.Model NOT LIKE 'V1%'

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 1

jennigirl (1/18/2012)
I was able to get the query results I need, but I am looking to now present the results as the total count of trouble tickets assigned to each person. I can get a total count of rows returned, but I am trying to get a count of tickets assigned to each person. I don't work a lot with aggregate functions so this one is stumping me.
SELECT DISTINCT TroubleTicket.TicketNumber, Asset.Person
FROM Asset
RIGHT JOIN TroubleTicket ON Asset.Id = TroubleTicket.AssetId
RIGHT JOIN Item ON (Asset.Person = Item.Person and Asset.CompanyId = Item.CompanyId)
WHERE (TroubleTicket.UT02 IS NOT NULL AND TroubleTicket.UT03 IS NOT NULL)
AND Item.Model NOT LIKE 'V1%'

So your select statement would be something like this.

SELECT Asset.Person, COUNT(*)

FROM Asset

RIGHT JOIN TroubleTicket ON Asset.Id = TroubleTicket.AssetId

RIGHT JOIN Item ON (Asset.Person = Item.Person and Asset.CompanyId = Item.CompanyId)

WHERE (TroubleTicket.UT02 IS NOT NULL AND TroubleTicket.UT03 IS NOT NULL)

AND Item.Model NOT LIKE 'V1%'

GROUP BY Asset.Person

Fraggle

jennigirl Old Hand Points: 372 More actions · Answer 2

jennigirl

Old Hand

Points: 372

January 18, 2012 at 8:23 am

#1435293

That makes more sense now. Thank you so much

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

jennigirl (1/18/2012)
I was able to get the query results I need, but I am looking to now present the results as the total count of trouble tickets assigned to each person. I can get a total count of rows returned, but I am trying to get a count of tickets assigned to each person. I don't work a lot with aggregate functions so this one is stumping me.
SELECT
DISTINCT TroubleTicket.TicketNumber,
Asset.Person
FROM
Asset
RIGHT JOIN TroubleTicket
ON Asset.Id = TroubleTicket.AssetId
RIGHT JOIN Item
ON (Asset.Person = Item.Person and Asset.CompanyId = Item.CompanyId)
WHERE
(TroubleTicket.UT02 IS NOT NULL AND TroubleTicket.UT03 IS NOT NULL)
AND Item.Model NOT LIKE 'V1%'

It's unusual for folks to use right joins - are you sure this is correct?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

jennigirl Old Hand Points: 372 More actions · Answer 4

I used a right join because I need the results from the TroubleTicket Table, but the Asset table is the one that links Asset to Item and Asset to TroubleTicket. I've never had any training on this and am pretty much teaching myself as I go, so there may be an easier way to do this that I have not learned yet.

eccentricDBA SSCarpal Tunnel Points: 4844 More actions · Answer 5

This might help. It represent joins as venn diagrams.

http://www.halfgaar.net/sql-joins-are-easy

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

ChrisM@home (1/18/2012)
jennigirl (1/18/2012)
I was able to get the query results I need, but I am looking to now present the results as the total count of trouble tickets assigned to each person. I can get a total count of rows returned, but I am trying to get a count of tickets assigned to each person. I don't work a lot with aggregate functions so this one is stumping me.
SELECT
DISTINCT TroubleTicket.TicketNumber,
Asset.Person
FROM
Asset
RIGHT JOIN TroubleTicket
ON Asset.Id = TroubleTicket.AssetId
RIGHT JOIN Item
ON (Asset.Person = Item.Person and Asset.CompanyId = Item.CompanyId)
WHERE
(TroubleTicket.UT02 IS NOT NULL AND TroubleTicket.UT03 IS NOT NULL)
AND Item.Model NOT LIKE 'V1%'
It's unusual for folks to use right joins - are you sure this is correct?

You should see some of my code. Combination of left, right, inner, outer, and full joins as well as "skipped ON" statements where multiple ON statements are listed together. One of these days, I'll have to write about it if I can actually come up with an example that something other than my right brain will actually understand. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Jeff Moden (2/13/2012)
ChrisM@home (1/18/2012)
jennigirl (1/18/2012)
I was able to get the query results I need, but I am looking to now present the results as the total count of trouble tickets assigned to each person. I can get a total count of rows returned, but I am trying to get a count of tickets assigned to each person. I don't work a lot with aggregate functions so this one is stumping me.
SELECT
DISTINCT TroubleTicket.TicketNumber,
Asset.Person
FROM
Asset
RIGHT JOIN TroubleTicket
ON Asset.Id = TroubleTicket.AssetId
RIGHT JOIN Item
ON (Asset.Person = Item.Person and Asset.CompanyId = Item.CompanyId)
WHERE
(TroubleTicket.UT02 IS NOT NULL AND TroubleTicket.UT03 IS NOT NULL)
AND Item.Model NOT LIKE 'V1%'
It's unusual for folks to use right joins - are you sure this is correct?
You should see some of my code. Combination of left, right, inner, outer, and full joins as well as "skipped ON" statements where multiple ON statements are listed together. One of these days, I'll have to write about it if I can actually come up with an example that something other than my right brain will actually understand. 😀

Heh only you, Jeff! If you can do this all in one query and it works, it would be a real showstopper 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 8

jennigirl (1/18/2012)
I can get a total count of rows returned, but I am trying to get a count of tickets assigned to each person. I don't work a lot with aggregate functions so this one is stumping me.

Consider using COUNT(*) OVER (PARTITION BY ________) which does NOT require the use of GROUP BY. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Cadavre SSC-Forever Points: 41690 More actions · Answer 9

Jeff Moden (2/15/2012)
jennigirl (1/18/2012)
I can get a total count of rows returned, but I am trying to get a count of tickets assigned to each person. I don't work a lot with aggregate functions so this one is stumping me.
Consider using COUNT(*) OVER (PARTITION BY ________) which does NOT require the use of GROUP BY. 😉

In my tests, I've found this to often be slower than a subquery with a group by joining on primary key back to the table. Not all the time, just often. So make sure you test on your actual data 🙂

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 10

Cadavre (2/16/2012)
Jeff Moden (2/15/2012)
jennigirl (1/18/2012)
I can get a total count of rows returned, but I am trying to get a count of tickets assigned to each person. I don't work a lot with aggregate functions so this one is stumping me.
Consider using COUNT(*) OVER (PARTITION BY ________) which does NOT require the use of GROUP BY. 😉
In my tests, I've found this to often be slower than a subquery with a group by joining on primary key back to the table. Not all the time, just often. So make sure you test on your actual data 🙂

Actually, true enough. The sort behind the scenes can really take some time. Thanks for the reminder.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)