December 9, 2012

Get count of columns MySQL

Question by user1868569

How can i get the count of columns in my table, searched google through, but can’t find a straight answer.

Thank you in advance.

Answer by Starx

Such information are located in INFORMATION_SCHEMA you can query a table called COLUMNS to get the number of columns in a database.

This query will give you the column count.

WHERE table_schema = 'DB_NAME' AND table_name = 'TABLE_NAME'
April 18, 2012

Mysql addition and add them as new column

Question by Mohan Sinfh

I want to fetch 2 coulmns count and do their total as a new column.
How can I do this?

i wrote this query, but this is returning wrong total.

SELECT count(case when `status`='1' then 1 else 0 end) AS HOT,
count(case when `status`='5' then 1 end) 
AS Special_Case,count(case when 1=1 then 1 end) AS TOTAL 
FROM `tbl_customer_conversation` group by 

Answer by Starx

COUNT will only give the times a record is matched, which in your query will always return 1. Because the values can either be 1 or 0. So count(1) is also 1 and count(0) is also 1.

AS, you want the total number of HOT cases and SPECIAL_CASE you have to use SUM.

    SUM(case when `status`='1' then 1 else 0 end) AS HOT,
    SUM(case when `status`='5' then 1 end) AS Special_Case,
    SUM(case when `status` = '1' or `status` = '5' then 1 end) AS TOTAL 
FROM `tbl_customer_conversation` 
group by date(`dt_added`),user_id
April 11, 2012

Counting rows from second table

Question by Goldie

I have two tables in mysql database


1 |red
2 |blue
3 |green
4 |white

and users

id|name  |group
1 |joe   |1
2 |max   |1
3 |anna  |2
4 |lisa  |2

So… joe and max are in the “red” group, anna and lisa are in the “blue” group.

How can I make simple listing of groups which would contain the number of
persons in that group
For example

red - 2
blue - 2
green - 0
white - 0

Answer by Mosty Mostacho

Give this a try:

select, count( from groups g
left join users u on =
group by,

Answer by Starx

This should work

INNER JOIN `users` u on =
April 6, 2012

How to count characters in array after while loop

Question by sdfgsdfg

Do u have any ideas how to count all characters in string $chars after while loop ?

$x = 0;
$b = 0;

while ($x++ < 10) {
  echo $chars = $item->zodziai[$b++];
  echo ' ';

echo strlen($chars);

Answer by Starx

Concatenate the characters then count them

$x = 0; $b = 0;
$char = ''; //define a variable to collect all the chars
while ($x++ < 10) {
   $char .= $item->zodziai[$b++]; //add to the char
   echo $chars = $item->zodziai[$b++]; echo ' ';
echo strlen($char); //then count all the array combined
April 3, 2012

How can I echo a COUNT query

Question by Tom Gillespie

I’m new to mySQL and as such am just looking for a very simple COUNT query which I haven’t found explained online with any real clarity.

What I’m looking to do is

COUNT the number of rows in my PASSENGER table where groupID = 0, and then be able to echo the numerical value that the count will return, how can I do this?

Answer by Starx

Something like this

$query = "SELECT COUNT(*) c FROM PASSENGER WHERE groupID = 0;";
$result = mysql_query($query);
$row = mysql_fetch_assoc($query);
echo $row['c']; //Here is your count

Please fill the form - I will response as fast as I can!