Problem with Pivot Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rockhardten - 25 Jul 2008 13:54 GMT

Hi all

Had this query:

SELECT Sire, [1] AS first, [2] AS second, [3] AS third, [4] AS fourth
FROM (SELECT Pos, Pos2, Sire
FROM dbo.Vista_2) p PIVOT (COUNT(Pos) FOR
Pos2 IN ([1], [2], [3], [4])) AS pvt

This query shows the following when executed:

Sire: 1st 2nd 3rd 4th
A Good Reason (BRZ)    1    1    0    1
Abou Zouz (USA)    0    0    2    2

Query shows the name of a horse, number of times was 1st, 2nd, 3rd and 4th,
but I dont know how to insert another column which show the total times the
horse run. The new query should look like this:

Sire: 1st 2nd 3rd 4th
Total
A Good Reason (BRZ)    1    1    0    1 3
Abou Zouz (USA)    0    0    2    2 4

Many thanks.

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 25 Jul 2008 14:02 GMT

Do you want the total times the horse came in the top 4 (which you can
derive after the query), or the total times the horse ran at all (which can
include finishes below 4th)?

For the former:

SELECT Sire, first, second, third, fourth, total =
(first+second+third+fourth)
FROM
(
your query
) x
ORDER BY Sire;

You could also limit "your query" to WHERE Pos2 <= 4 ...

For the latter, you will need to include those numbers in your original
query, e.g.

SELECT Sire, Pos, Pos2 = CASE WHEN Pos2 > 4 THEN 5 ELSE Pos2 END

Then you can include the worse finishes in a single bucket for the pivot.

A

On 7/25/08 8:54 AM, in article
888DAE39-2942-4970-AAA7-14325BF2E5CE@microsoft.com, "rockhardten"

> Hi all
>
[quoted text clipped - 21 lines]
>
> Many thanks.

Reply to this Message

rockhardten - 25 Jul 2008 14:10 GMT

Many thanks for fast answer,

The query will show up to 30 positions, i was just shortening it for an easy
read. Also, want to make the total column to appear in this query, not in a
derived one.

> Do you want the total times the horse came in the top 4 (which you can
> derive after the query), or the total times the horse ran at all (which can
[quoted text clipped - 49 lines]
> >
> > Many thanks.

Reply to this Message

Plamen Ratchev - 25 Jul 2008 15:06 GMT

Here is one way:

SELECT Sire, [1] AS first, [2] AS second, [3] AS third, [4] AS fourth,
total_count
FROM (SELECT Pos, Pos2, Sire, COUNT(*) OVER(PARTITION BY Sire) AS
total_count
FROM dbo.Vista_2) p
PIVOT (COUNT(Pos) FOR
Pos2 IN ([1], [2], [3], [4])) AS pvt;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

rockhardten - 25 Jul 2008 16:10 GMT

Both work perfectly, I prefer this one as includes total in the same query,
many thanks, apreciated.

> Here is one way:
>
[quoted text clipped - 10 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message

rockhardten - 27 Jul 2008 22:39 GMT

Hi Again,

All works fine, but know I have a problem trying to get the percentages.

I use this query:
SELECT TOP (100) PERCENT Sire, first, second, third, fourth, fifth,
total_count, first / total_count AS total
FROM dbo.Vista5f2

and the following appear:

Horse 1st 2nd 3rd 4th 5th
total_count total
Inchinor 1 1 0 0 0 2 0
Compton 1 1 0 1 1 4 0

The percentage shows a 0 value, im becoming very frustrated, tried
everything. Any view?

Many thanks

> Hi all
>
[quoted text clipped - 21 lines]
>
> Many thanks.

Reply to this Message

Plamen@sqlstudio.com - 27 Jul 2008 23:10 GMT

This is because of the integer division. A couple ways to solve it:

SELECT 1.0 * first / total_count AS total ...

or

SELECT CAST(first AS DECIMAL(5, 2)) / total_count AS total ...

You can adjust precision/scale as needed.

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message