count on several colum

Question

count on several colum

ilbric-762008

SSC Rookie

Points: 26
More actions
September 15, 2010 at 2:06 am

#387787

Hi all,
I'm working on a small project which happened to use a SQL Server 2008 DB developed in the past and I'm not allowed to change its structure.
So, I have the following scenario:
1) a table T with 8 fields (BA1, BA2, BA3, BA4, BA5, CA1, CA2, CA3, CA4, CA5) INT;
2) I need to count the occurence of every number stored in every column (SELECT COUNT(BA1) FROM T GROUP BY BA1)
3) I need the sum of the count of the preceeding point (sum of count of BA* and sum of count of CA*), that is, I need to find the total occurence of every number stored in five columns
4) I know I can extract the count for every column, store them in a temp table and them summarize every column but I'd prefer a more elegant solution, if that solution exists... 😉
So, any idea will be gratly appreciate!
Thanks,
ilbric

Viewing 7 posts - 1 through 7 (of 7 total)

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

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

There are numerous ways of getting the aggregates that you need, but how do you expect the output to look? If you run this, then you'll see what I mean:

DROP TABLE #Sample

CREATE TABLE #Sample

(BA1 INT, BA2 INT, BA3 INT, BA4 INT, BA5 INT, CA1 INT, CA2 INT, CA3 INT, CA4 INT, CA5 INT)

INSERT INTO #Sample

(BA1, BA2, BA3, BA4, BA5, CA1, CA2, CA3, CA4, CA5)

SELECT 1, 2, 3, 4, 5, 10, 20, 30, 40, 50 UNION ALL

SELECT 1, 2, 6, 4, 5, 10, 20, 30, 40, 50 UNION ALL

SELECT 1, 4, 6, 4, 5, 10, 20, 30, 40, 50 UNION ALL

SELECT 2, 4, 9, 4, 5, 10, 20, 30, 40, 50 UNION ALL

SELECT 3, 4, 9, 4, 5, 10, 20, 30, 40, 50 UNION ALL

SELECT 3, 6, 9, 4, 5, 10, 20, 30, 40, 50 UNION ALL

SELECT 3, 6, 12, 4, 5, 10, 20, 30, 40, 50

SELECT

BA1, RowsBA1 = COUNT(BA1) OVER (PARTITION BY BA1),

BA2, RowsBA2 = COUNT(BA2) OVER (PARTITION BY BA2),

BA3, RowsBA3 = COUNT(BA3) OVER (PARTITION BY BA3)

FROM #Sample

^{“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

ilbric-762008 SSC Rookie Points: 26 More actions · Answer 2

Hi, thanks for your promptly reply!

The result I need, based on your sample, if it is possible to obtain, would be:

Number BA CA

1 3 (3 on BA1) 0

2 4 (2 on BA1 + 2 on BA2) 0

3 5 (4 on BA1 + 1 on BA3) 0

4

... an so on till...

50 0 9

Regards,

ilbric

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

If you're unsure about any part of this, just ask 🙂

-- sample data (not part of the solution)

DROP TABLE #Sample

CREATE TABLE #Sample

(BA1 INT, BA2 INT, BA3 INT, BA4 INT, BA5 INT, CA1 INT, CA2 INT, CA3 INT, CA4 INT, CA5 INT)

INSERT INTO #Sample

(BA1, BA2, BA3, BA4, BA5, CA1, CA2, CA3, CA4, CA5)

SELECT 1, 2, 3, 4, 5, 10, 20, 30, 40, 50 UNION ALL

SELECT 1, 2, 6, 4, 5, 10, 20, 30, 40, 50 UNION ALL

SELECT 1, 4, 6, 4, 5, 10, 20, 30, 40, 50 UNION ALL

SELECT 2, 4, 9, 4, 5, 10, 20, 30, 40, 50 UNION ALL

SELECT 3, 4, 9, 4, 5, 10, 20, 30, 40, 50 UNION ALL

SELECT 3, 6, 9, 4, 5, 10, 20, 30, 40, 50 UNION ALL

SELECT 3, 6, 12, 4, 5, 10, 20, 30, 40, 50

-- solution

;WITH NormalisedData AS (

SELECT [ColumnName] = 'BA1', [Number] = BA1 FROM #Sample UNION ALL

SELECT 'BA2', BA2 FROM #Sample UNION ALL

SELECT 'BA3', BA3 FROM #Sample UNION ALL

SELECT 'BA4', BA4 FROM #Sample UNION ALL

SELECT 'BA5', BA5 FROM #Sample UNION ALL

SELECT 'CA1', CA1 FROM #Sample UNION ALL

SELECT 'CA2', CA2 FROM #Sample UNION ALL

SELECT 'CA3', CA3 FROM #Sample UNION ALL

SELECT 'CA4', CA4 FROM #Sample UNION ALL

SELECT 'CA5', CA5 FROM #Sample

)

SELECT [Number],

[BA] = SUM(CASE WHEN [ColumnType] = 'BA' THEN [RowsCounted] ELSE 0 END),

[CA] = SUM(CASE WHEN [ColumnType] = 'CA' THEN [RowsCounted] ELSE 0 END)

FROM (

SELECT [ColumnType] = LEFT(ColumnName, 2), [Number], [RowsCounted] = COUNT(*)

FROM NormalisedData

GROUP BY LEFT(ColumnName, 2), [Number]

) d

GROUP BY [Number]

ORDER BY [Number]

^{“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

ilbric-762008 SSC Rookie Points: 26 More actions · Answer 4

Well, simply perfect!!

Actually I have a question... could you explain the following row, the first in the CTE statement?

SELECT [ColumnName] = 'BA1', [Number] = BA1 FROM #Sample UNION ALL

Many thanks and regards.

ilbric

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

ilbric-762008 (9/15/2010)
Well, simply perfect!!
Actually I have a question... could you explain the following row, the first in the CTE statement?
SELECT [ColumnName] = 'BA1', [Number] = BA1 FROM #Sample UNION ALL
Many thanks and regards.
ilbric

It means: Take the unrestricted set consisting of column 'BA1' and call it [Number]; create a new column called [ColumnName] populated with the string 'BA1'. Instead of using a column to identify the data, you're now using a value in a new column.

The point is that to do this analysis, you first have to normalise the data for BA* columns and CA* columns. There's a hint in the name assigned to the CTE.

You could populate the new column with 'BA' (and 'CA', of course), I just thought it would be easier to follow if the full column name from the input table was stored.

Does this help?

^{“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

ilbric-762008 SSC Rookie Points: 26 More actions · Answer 6

Thanks a lot for everything, included the last explanation. Now I must get an hour or two and take a much deeper look to your solution.

Regards.

ilbric