December 30, 2010 at 6:27 am
Is it possible to count columns with occurrences in a cross-tab query? The occurrence is: score is not null. At the bottom of each column I like to see the number of occurrences as described above.
Robert
December 30, 2010 at 7:08 am
Even if there is no "bottom of the column", you could either use WITH ROLLUP or you could calculate the values within your CrossTab. In order to control that total being the last row of your query I guess you'll need an additional column to sort by.
December 30, 2010 at 11:15 am
I am not sure I get the grips. To clear things up I give you the following code:
SELECT TOP (100) PERCENT Koersnaam, COUNT(Koersid) AS Totaal, MAX(CASE WHEN Jaar = 1948 THEN Score ELSE NULL END) AS [1948],
MAX(CASE WHEN Jaar = 1949 THEN Score ELSE NULL END) AS [1949],
MAX(CASE WHEN Jaar = 1950 THEN Score ELSE NULL END) AS [1950],
MAX(CASE WHEN Jaar = 1951 THEN Score ELSE NULL END) AS [1951],
MAX(CASE WHEN Jaar = 1952 THEN Score ELSE NULL END) AS [1952]
FROM dbo.vwPernodUnion
GROUP BY Koersnaam, Koersid
ORDER BY Koersnaam
gives the following result:
Competition Total 1948 1949 1950 1951 1952
Criterium du
Dauphiné Libéré26NULLNULLNULLNULLNULL
De Ronde van
Zwitserland23NULLTdSTdSTdSTdS
Giro d'Italia35GdIGdIGdIGdIGdI
Parijs-Luxemburg7NULLNULLNULLNULLNULL
Parijs-Nice28NULLNULLNULLNULLNULL
Tirreno Adriatico7NULLNULLNULLNULLNULL
Tour de France40TdFTdFTdFTdFTdF
Vuelta a Espana25NULLNULLNULLNULLNULL
Now I want a last row called Aantal which gives in 1948: 2, 1949: 3 etc., which stands for the number of competitions that year (so counting the not NULL values).
A rollup gives an error, a with rollup gives an error, but perhaps I use the command in the wrong way (newbie...).
I hope that this information clears things up to get the right answer.
Robert
December 30, 2010 at 11:41 am
Please provide ready tu use sample data so we have something to test our solutions against.
For details on how to do please see the first link in my signature.
I tried using the sample data from the last related post but there are som columns missing...;-)
December 30, 2010 at 11:47 am
Now I want a last row called Aantal which gives in 1948: 2, 1949: 3 etc., which stands for the number of competitions that year (so counting the not NULL values).
My suggestion would be to capture your results in a #temp table, then do something like this:
SELECT * FROM #temp
UNION ALL
SELECT
'Aantal',
sum(case when [1948] is not null then 1 else 0 end) as [1948],
sum(case when [1949] is not null then 1 else 0 end) as [1949],
etc, etc
FROM #temp
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 30, 2010 at 12:43 pm
This is the raw material before the cross tab query. I don't know how to bring it any further like the etiquette (text mode? query analyzer?). perhaps you can past it in;
Create Table tblTest
(
Jaar int,
Koersid int,
Score nvarchar(3),
Koersnaam nvarchar(50)
Select'1948','55','TdF','Tour de France',Union All
Select'1948','56','GdI','Giro d''Italia',Union All
Select'1949','55','TdF','Tour de France',Union All
Select'1949','56','GdI','Giro d''Italia',Union All
Select'1949','61','TdS','De Ronde van Zwitserland',Union All
Select'1950','55','TdF','Tour de France',Union All
Select'1950','61','TdS','De Ronde van Zwitserland',Union All
Select'1951','55','TdF','Tour de France',Union All
Select'1951','61','TdS','De Ronde van Zwitserland',Union All
Select'1952','55','TdF','Tour de France',Union All
Select'1952','56','GdI','Giro d''Italia',Union All
Select'1952','61','TdS','De Ronde van Zwitserland',Union All
If this won't work let me know - I'll try something else.
Robert
December 30, 2010 at 1:00 pm
Based on your sample data and Bob's code snippet I'd use the following aproach:
;
WITH cte AS
(
SELECT
Koersnaam,
COUNT(Koersid) AS Totaal,
MAX(CASE WHEN Jaar = 1948 THEN Score ELSE NULL END) AS [1948],
MAX(CASE WHEN Jaar = 1949 THEN Score ELSE NULL END) AS [1949],
MAX(CASE WHEN Jaar = 1950 THEN Score ELSE NULL END) AS [1950],
MAX(CASE WHEN Jaar = 1951 THEN Score ELSE NULL END) AS [1951],
MAX(CASE WHEN Jaar = 1952 THEN Score ELSE NULL END) AS [1952]
FROM @tbl
GROUP BY Koersnaam, Koersid
UNION ALL
SELECT 'total' AS Koersnaam,
COUNT(*),
CAST(SUM(CASE WHEN Jaar = 1948 THEN 1 ELSE 0 END) AS NVARCHAR(3)) AS [1948],
CAST(SUM(CASE WHEN Jaar = 1949 THEN 1 ELSE 0 END) AS NVARCHAR(3)) AS [1949],
CAST(SUM(CASE WHEN Jaar = 1950 THEN 1 ELSE 0 END) AS NVARCHAR(3)) AS [1950],
CAST(SUM(CASE WHEN Jaar = 1951 THEN 1 ELSE 0 END) AS NVARCHAR(3)) AS [1951],
CAST(SUM(CASE WHEN Jaar = 1952 THEN 1 ELSE 0 END) AS NVARCHAR(3)) AS [1952]
FROM @tbl
)
SELECT *
FROM cte
ORDER BY CASE WHEN Koersnaam ='total' THEN 1 ELSE 0 END, Koersnaam
December 30, 2010 at 1:00 pm
I encounter two problems with the temp solution: union demands the same number of columns and score is an nvarchar, so the isnull gives an error (it evaluates as an integer??).
Is there a solution?
Robert
December 30, 2010 at 1:12 pm
Looks good, but remember I am a newbie. So things are going to fast for me. First the beginning: ; with cte as - what is the meaning of this? cte is the temporary table? How do I get the data in a temporary table?
Then is 'total' what I mean by 'aantal' and counts vertical? Furthermore as Koersnaam so it will be the last row in combination with the union command.
Sorry for the level of questioning, but with your help it must become better in the future.
Robert
December 30, 2010 at 1:17 pm
(1) You must ensure that the second query (following the UNION ALL) produces the same number of columns as contained in the temp table, even if that means forcing nulls or blanks for unused columns in the final row.
(2) CAST or CONVERT the counts in the last row as NVARCHAR like so
convert(nvarchar,sum(case when [1948] is not null then 1 else 0 end)) as [1948]
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 30, 2010 at 1:21 pm
CTE is short for "Common table expression", which is an alternative to using a subquery. The CTE is not a temporary table; it produces a result set which may be queried as if it were a table. It may help you conceptually to think of a CTE as an inline "VIEW" that only exists for the life of the query that references it.
To put your results into a temporary table, use SELECT / INTO / FROM as shown below
SELECT column_A, column_B, column_C
INTO #TEMP
FROM CTE
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 30, 2010 at 1:28 pm
r_slot (12/30/2010)
Looks good, but remember I am a newbie. So things are going to fast for me. First the beginning: ; with cte as - what is the meaning of this? cte is the temporary table? How do I get the data in a temporary table?Then is 'total' what I mean by 'aantal' and counts vertical? Furthermore as Koersnaam so it will be the last row in combination with the union command.
Sorry for the level of questioning, but with your help it must become better in the future.
Robert
A cte (at least in this case) is nothing more than a subquery, just sometimes easier to read. Search BOL (BooksOnLine, the SQL Sever help system usually installed together with SQL Server) for "common table expressions" for more details (I'd use "Using Common Table Expressions" to start with, not "Recursive Queries"...) . It is not a temp table. Therefore, you don't have to get the data into it. All you need to do is to change the referenced table from @tbl to your original source table.
'total' is just the value for the column Koersnaam. If you'd like to change it, just do so (but make sure to change it in the CASE statement in the final ORDER BY).
The CASE statement in the final ORDER BY is used to make sure this row will be the last one in your query. It basically assigns a Zero to all "non-total" rows and a 1 to the total row. When sorting in ascending order, the total row will be the last one. The Koersnaam in the ORDER BY statement is used to sort the values before the total row.
There is nothing to be sorry for. You did a great job providing the sample data and describing what you're looking for. Asking questions is never a bad thing and you have to start at some level to get better. 😉
December 30, 2010 at 1:32 pm
The Dixie Flatline (12/30/2010)
CTE is short for "Common table expression", which is an alternative to using a subquery. The CTE is not a temporary table; it produces a result set which may be queried as if it were a table. It may help you conceptually to think of a CTE as an inline "VIEW" that only exists for the life of the query that references it.To put your results into a temporary table, use SELECT / INTO / FROM as shown below
SELECT column_A, column_B, column_C
INTO #TEMP
FROM CTE
Hey Bob,
it seems like we've got slightly out of sync... (posting similar stuff just a few minutes apart). I'll stop posting here for the next 30min to get back in sync. ;-):-D
December 30, 2010 at 1:46 pm
Guys,
I did the following and it worked!!! You did a great job learning me this, thanks a lot to the joined forces LutzM and FlatDixie:
SELECT
Koersnaam,
COUNT(Koersid) AS Totaal,
MAX(CASE WHEN Jaar = 1948 THEN Score ELSE NULL END) AS [1948],
MAX(CASE WHEN Jaar = 1949 THEN Score ELSE NULL END) AS [1949],
MAX(CASE WHEN Jaar = 1950 THEN Score ELSE NULL END) AS [1950],
MAX(CASE WHEN Jaar = 1951 THEN Score ELSE NULL END) AS [1951],
MAX(CASE WHEN Jaar = 1952 THEN Score ELSE NULL END) AS [1952]
FROM tblTest
GROUP BY Koersnaam, Koersid
UNION ALL
SELECT 'Aantal Koersen' AS Koersnaam,
NULL, -- null because this is a non-relevant number
CAST(SUM(CASE WHEN Jaar = 1948 THEN 1 ELSE 0 END) AS NVARCHAR(3)) AS [1948],
CAST(SUM(CASE WHEN Jaar = 1949 THEN 1 ELSE 0 END) AS NVARCHAR(3)) AS [1949],
CAST(SUM(CASE WHEN Jaar = 1950 THEN 1 ELSE 0 END) AS NVARCHAR(3)) AS [1950],
CAST(SUM(CASE WHEN Jaar = 1951 THEN 1 ELSE 0 END) AS NVARCHAR(3)) AS [1951],
CAST(SUM(CASE WHEN Jaar = 1952 THEN 1 ELSE 0 END) AS NVARCHAR(3)) AS [1952]
FROM tblTest
Thanks again,
Robert
December 30, 2010 at 1:49 pm
Guys,
I did the following and it worked!!! You did a great job learning me this, thanks a lot to the joined forces LutzM and FlatDixie:
SELECT
Koersnaam,
COUNT(Koersid) AS Totaal,
MAX(CASE WHEN Jaar = 1948 THEN Score ELSE NULL END) AS [1948],
MAX(CASE WHEN Jaar = 1949 THEN Score ELSE NULL END) AS [1949],
MAX(CASE WHEN Jaar = 1950 THEN Score ELSE NULL END) AS [1950],
MAX(CASE WHEN Jaar = 1951 THEN Score ELSE NULL END) AS [1951],
MAX(CASE WHEN Jaar = 1952 THEN Score ELSE NULL END) AS [1952]
FROM tblTest
GROUP BY Koersnaam, Koersid
UNION ALL
SELECT 'Aantal Koersen' AS Koersnaam,
NULL, -- null because this is a non-relevant number
CAST(SUM(CASE WHEN Jaar = 1948 THEN 1 ELSE 0 END) AS NVARCHAR(3)) AS [1948],
CAST(SUM(CASE WHEN Jaar = 1949 THEN 1 ELSE 0 END) AS NVARCHAR(3)) AS [1949],
CAST(SUM(CASE WHEN Jaar = 1950 THEN 1 ELSE 0 END) AS NVARCHAR(3)) AS [1950],
CAST(SUM(CASE WHEN Jaar = 1951 THEN 1 ELSE 0 END) AS NVARCHAR(3)) AS [1951],
CAST(SUM(CASE WHEN Jaar = 1952 THEN 1 ELSE 0 END) AS NVARCHAR(3)) AS [1952]
FROM tblTest
Thanks again,
Robert
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply