adding Group by result to each other
-
i have a table which was created in MsSql 2005 Express.. http://img441.imageshack.us/f/numaralar.jpg/[^] i have numbers in 6 columns..i can group every column and count which number was selected how many times in columns... but for example number 10 exist in column1 and column2 and column3 also..how i can add that count results that i found for every column seperately to each other to find the total of the number 10 selected ...
-
i have a table which was created in MsSql 2005 Express.. http://img441.imageshack.us/f/numaralar.jpg/[^] i have numbers in 6 columns..i can group every column and count which number was selected how many times in columns... but for example number 10 exist in column1 and column2 and column3 also..how i can add that count results that i found for every column seperately to each other to find the total of the number 10 selected ...
Are you looking for something like this
SELECT
CASE WHEN NUM1 = 10 THEN 1 ELSE 0 END +
CASE WHEN NUM2 = 10 THEN 1 ELSE 0 END +
CASE WHEN NUM3 = 10 THEN 1 ELSE 0 END +
CASE WHEN NUM4 = 10 THEN 1 ELSE 0 END +
CASE WHEN NUM5 = 10 THEN 1 ELSE 0 END +
CASE WHEN NUM6 = 10 THEN 1 ELSE 0 END
AS TOTALFROM MYTABLE
-
Are you looking for something like this
SELECT
CASE WHEN NUM1 = 10 THEN 1 ELSE 0 END +
CASE WHEN NUM2 = 10 THEN 1 ELSE 0 END +
CASE WHEN NUM3 = 10 THEN 1 ELSE 0 END +
CASE WHEN NUM4 = 10 THEN 1 ELSE 0 END +
CASE WHEN NUM5 = 10 THEN 1 ELSE 0 END +
CASE WHEN NUM6 = 10 THEN 1 ELSE 0 END
AS TOTALFROM MYTABLE
thanks for reply friend..when i write that code
SELECT num1,count(*) FROM NumaraBilgileri
GROUP BY num1i got that http://img827.imageshack.us/f/num1column.png/[^] as u see i have 22 numbers there..it is just for column1 and i have 5 more columns.. i mean that after that Select executed i have number 10 here 4 times..and in column2 i have 5 times number 10 so total makes 9..and so on.. i want to get the all numbers total in that table from 1 to 49..how i can get it
-
thanks for reply friend..when i write that code
SELECT num1,count(*) FROM NumaraBilgileri
GROUP BY num1i got that http://img827.imageshack.us/f/num1column.png/[^] as u see i have 22 numbers there..it is just for column1 and i have 5 more columns.. i mean that after that Select executed i have number 10 here 4 times..and in column2 i have 5 times number 10 so total makes 9..and so on.. i want to get the all numbers total in that table from 1 to 49..how i can get it
Then try using UNION Query like
SELECT A.NUM, SUM(A.TOTAL) AS TOTAL FROM (
SELECT num1 AS NUM,count(*) AS TOTAL FROM NumaraBilgileri
GROUP BY num1
UNION ALL
SELECT num2 AS NUM,count(*) AS TOTAL FROM NumaraBilgileri
GROUP BY num2
) AS A
GROUP BY A.NUMYou need to create union for all fields.
-
Then try using UNION Query like
SELECT A.NUM, SUM(A.TOTAL) AS TOTAL FROM (
SELECT num1 AS NUM,count(*) AS TOTAL FROM NumaraBilgileri
GROUP BY num1
UNION ALL
SELECT num2 AS NUM,count(*) AS TOTAL FROM NumaraBilgileri
GROUP BY num2
) AS A
GROUP BY A.NUMYou need to create union for all fields.
thanks for the reply man..that was the what exactly i mean now it works for all columns
