count and grouping

[nu2php]

Hi all, Please help if you are able to, with the problem I’m having below.
I need to find all 1’s in Col3 grouped by the value in Col2. I also need to know the total number of rows for the value in Col2.

Col1 Col2 Col3
ABC 1234 2
ABC 1234 2
ABC 5678 2
ABC 5678 1

Desired Output
eg
Col1 Col2 Total 1’s Total Rows (Col2)
ABC 1234 0 2
ABC 5678 1 2

I’ve tried various queries with count and grouping but it doesn’t give me the output I need.
Thanks

[Cups]

That is an sql question, if you try asking it in the sql or mysql forum then you will likely get a fuller answer.

[nu2php]

My bad...
Can this be moved...or should i create a new thread in the MySQL section

[nu2php]

Hi all, Please help if you are able to, with the problem I’m having below.
I need to find all 1’s in Col3 grouped by the value in Col2. I also need to know the total number of rows for the value in Col2.

Col1 Col2 Col3
ABC 1234 2
ABC 1234 2
ABC 5678 2
ABC 5678 1

Desired Output
eg
Col1 Col2 Total 1’s Total Rows(Col2)
ABC 1234 0___________ 2
ABC 5678 1 ___________ 2

I’ve tried various queries with count and grouping but it doesn’t give me the output I need.
Thanks

[Cups]

There are some of the sql guys that find the sql questions in here, but mostly you'll get a PHPer giving it their best shot.

[swampBoogie]

select col1,
         col2,
         sum(case when col3 = 1 then 1 else 0 end),
         count(*)
  from t
 group by col1,col2

[nu2php]

WOW thanks a million.
It works perfectly.
I was going nuts using count and group and every other thing i could think off.
Thanks again

[Salathe]

I'm not a MySQL expert by any stretch of the imagination, so the following might be horribly inefficient (or just plain not work!):

SELECT
	t1.Col1, t1.Col2, 
	COUNT(t2.Col3) AS `Total 1's`,
	COUNT(t1.Col2) AS `Total Rows (Col2)`
FROM 
	my_table AS t1
LEFT JOIN
	(SELECT Col3 FROM my_table WHERE Col3 = 1)
	AS t2 ON t2.Col3 = t1.Col3
GROUP BY
	t1.Col2

Edit: Must learn to wake up before thinking about SQL... thanks r937 for making me remember CASE

[r937]

SELECT Col1
     , Col2
     , SUM(CASE WHEN Col3 = 1
                THEN 1 ELSE 0 END) AS Total_1s
     , COUNT(*)                    AS Total_Rows
  FROM my_table
GROUP
    BY Col1
     , Col2

an alternative that is just a wee bit more obscure --

SELECT Col1
     , Col2
     , COUNT(*) - 
             COUNT(NULLIF(Col3,1)) AS Total_1s
     , COUNT(*)                    AS Total_Rows
  FROM my_table
GROUP
    BY Col1
     , Col2

[AnthonySterling]

.....must......hire.......Rudy.....

[nu2php]

Thanks for the help guys.
much appreciated

[r937]

see what happens when you re-post in a separate forum? we end up having to merge the threads anyway, and then the interleaving of replies gets all jumbled

please, if you've posted in the wrong forum, push the little red triangle and ask that the thread be moved