Author |
Topic |
sikharma13
Starting Member
44 Posts |
Posted - 2013-10-30 : 01:38:21
|
for example i have table = table1-------name------|-----month1-----|-----month2-----|maria--dela-cruz-|----december----|----january-----|maria--dela-cruz-|----------------|----january-----|maria--dela-cruz-|----------------|----january-----|pedro--dela-cruz-|-----january----|----------------|pedro--dela-cruz-|-----january----|----december----|pedro--dela-cruz-|-----january----|----december----|desired output:-------name------|----no_month1---|-----no_month2-----|maria--dela-cruz-|---------1------|---------3---------|pedro--dela-cruz-|---------3------|---------2---------|please... i need a query in sql.. thanksVFP9.0 via MySQL 5.0 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-30 : 01:47:04
|
[code]SELECT name,COUNT(month1) AS no_month1,COUNT(month2) AS no_month2FROM TableGROUP BY name[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sikharma13
Starting Member
44 Posts |
Posted - 2013-10-30 : 01:55:07
|
quote: Originally posted by visakh16
SELECT name,COUNT(month1) AS no_month1,COUNT(month2) AS no_month2FROM TableGROUP BY name ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
sir, this is your query's result..-------name------|----no_month1---|-----no_month2-----|maria--dela-cruz-|---------3------|---------3---------|pedro--dela-cruz-|---------2------|---------2---------|still ddnt get what i wanted.. still thank you!VFP9.0 via MySQL 5.0 |
|
|
sikharma13
Starting Member
44 Posts |
Posted - 2013-10-30 : 02:00:05
|
if may... i need to also count the zero's.. like this..-------name------|-----month1-----|-----month2-----|maria--dela-cruz-|----december----|----january-----|maria--dela-cruz-|----------------|----january-----|maria--dela-cruz-|----------------|----january-----|pedro--dela-cruz-|-----january----|----------------|pedro--dela-cruz-|-----january----|----december----|pedro--dela-cruz-|-----january----|----december----|juan---dela-cruz-|----------------|----------------|desired output:-------name------|----no_month1---|-----no_month2-----|maria--dela-cruz-|---------1------|---------3---------|pedro--dela-cruz-|---------3------|---------2---------|juan---dela-cruz-|---------0------|---------0---------|VFP9.0 via MySQL 5.0 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-30 : 02:00:16
|
quote: Originally posted by sikharma13
quote: Originally posted by visakh16
SELECT name,COUNT(month1) AS no_month1,COUNT(month2) AS no_month2FROM TableGROUP BY name ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
sir, this is your query's result..-------name------|----no_month1---|-----no_month2-----|maria--dela-cruz-|---------3------|---------3---------|pedro--dela-cruz-|---------2------|---------2---------|still ddnt get what i wanted.. still thank you!VFP9.0 via MySQL 5.0
Then i think you've not posted proper sample dataIs the value NULL for month1 in 2nd and 3rd rows? or is it blank?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sikharma13
Starting Member
44 Posts |
Posted - 2013-10-30 : 02:03:32
|
quote: Originally posted by visakh16
quote: Originally posted by sikharma13
quote: Originally posted by visakh16
SELECT name,COUNT(month1) AS no_month1,COUNT(month2) AS no_month2FROM TableGROUP BY name ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
sir, this is your query's result..-------name------|----no_month1---|-----no_month2-----|maria--dela-cruz-|---------3------|---------3---------|pedro--dela-cruz-|---------2------|---------2---------|still ddnt get what i wanted.. still thank you!VFP9.0 via MySQL 5.0
Then i think you've not posted proper sample dataIs the value NULL for month1 in 2nd and 3rd rows? or is it blank?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
it's a NULL value.. sorry for having incomplete details.. :)VFP9.0 via MySQL 5.0 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-30 : 03:20:06
|
see illustration in SQL Serverdeclare @t table(name varchar(30),month1 varchar(30),month2 varchar(30))insert @tvalues('maria--dela-cruz','december','january'),('maria--dela-cruz',NULL,'january'),('maria--dela-cruz',NULL,'january'),('pedro--dela-cruz','january',NULL),('pedro--dela-cruz','january','december'),('pedro--dela-cruz','january','december'),('juan---dela-cruz',NULL,NULL)SELECT name,COUNT(month1) AS no_month1,COUNT(month2) AS no_month2FROM @tGROUP BY nameoutput-----------------------------------------name no_month1 no_month2-----------------------------------------juan---dela-cruz 0 0maria--dela-cruz 1 3pedro--dela-cruz 3 2 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sikharma13
Starting Member
44 Posts |
Posted - 2013-10-30 : 03:36:07
|
quote: Originally posted by visakh16 see illustration in SQL Serverdeclare @t table(name varchar(30),month1 varchar(30),month2 varchar(30))insert @tvalues('maria--dela-cruz','december','january'),('maria--dela-cruz',NULL,'january'),('maria--dela-cruz',NULL,'january'),('pedro--dela-cruz','january',NULL),('pedro--dela-cruz','january','december'),('pedro--dela-cruz','january','december'),('juan---dela-cruz',NULL,NULL)SELECT name,COUNT(month1) AS no_month1,COUNT(month2) AS no_month2FROM @tGROUP BY nameoutput-----------------------------------------name no_month1 no_month2-----------------------------------------juan---dela-cruz 0 0maria--dela-cruz 1 3pedro--dela-cruz 3 2 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Sir, how about if its blank instead of null value?VFP9.0 via MySQL 5.0 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-30 : 03:45:03
|
DO you mean you dont even know what is in your db?Anyways here you godeclare @t table(name varchar(30),month1 varchar(30),month2 varchar(30))insert @tvalues('maria--dela-cruz','december','january'),('maria--dela-cruz','','january'),('maria--dela-cruz','','january'),('pedro--dela-cruz','january',''),('pedro--dela-cruz','january','december'),('pedro--dela-cruz','january','december'),('juan---dela-cruz','','')SELECT name,COUNT(NULLIF(month1,'')) AS no_month1,COUNT(NULLIF(month2,'')) AS no_month2FROM @tGROUP BY nameoutput--------------------------------------------------name no_month1 no_month2--------------------------------------------------juan---dela-cruz 0 0maria--dela-cruz 1 3pedro--dela-cruz 3 2 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sikharma13
Starting Member
44 Posts |
Posted - 2013-10-30 : 04:01:23
|
sorry sir visakh.. im only 13 years old.. as u see.. im just a kid..just for u to know.. :))VFP9.0 via MySQL 5.0 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-30 : 04:10:16
|
quote: Originally posted by sikharma13 sorry sir visakh.. im only 13 years old.. as u see.. im just a kid..just for u to know.. :))VFP9.0 via MySQL 5.0
what has that to do with seeing whats in your db?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sikharma13
Starting Member
44 Posts |
Posted - 2013-10-30 : 04:24:03
|
i mean that is why i am that importunate.. xPso sorry.. :))i do still have many questions to u sir.. if it is ok.. VFP9.0 via MySQL 5.0 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-30 : 04:26:29
|
quote: Originally posted by sikharma13 i mean that is why i am that importunate.. xPso sorry.. :))i do still have many questions to u sir.. if it is ok.. VFP9.0 via MySQL 5.0
Asking questions is goodBut there should be a genuine attempt from your end before asking for answer.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sikharma13
Starting Member
44 Posts |
Posted - 2013-10-30 : 04:28:53
|
as u notice.. all my questions are linked...im getting confused when i merge all of them.. later ill type all details is it ok??like the whole thing.. for me to get satisfied.. hahaha thanks thou! :)VFP9.0 via MySQL 5.0 |
|
|
sikharma13
Starting Member
44 Posts |
Posted - 2013-10-30 : 04:31:15
|
you have facebook sir??? ill add you using my big brothers account...i dont have fb but my brother has.. for me to send u pictures insteadof explaining.. for me to say to clearly.. thank you for your understanding..VFP9.0 via MySQL 5.0 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-30 : 08:23:09
|
quote: Originally posted by sikharma13 you have facebook sir??? ill add you using my big brothers account...i dont have fb but my brother has.. for me to send u pictures insteadof explaining.. for me to say to clearly.. thank you for your understanding..VFP9.0 via MySQL 5.0
Please post your questions here as that would make it possible for others to see as well and reply------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|