• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2171
  • Last Modified:

One Table, Multiple Count(*), MySql

Ok,
This is what I have ..

Table viHomeVisitbyPara
ParaID | Week1 | Week2 | Week3  |  Week4 | Week5 |  Week6 |  (all the way to Week30
2         |   1       |     2      |       3       |    4       |   Null   |              |
4         |  1        |      2     |  Null        |        Null     |


You get the picture

I'm looking for  a query what will give me a count of those ParaID  that have a certain number for a certain week-- but I want this count all the way to Week30

If it was just one week.. well
it would be easy

SELECT COUNT(*) as Week1Count FROM viHomeVisitsbyPara WHERE Week1=1  

How can you do this for 30 columns?
0
hbojorquezgeek
Asked:
hbojorquezgeek
  • 5
  • 3
  • 2
1 Solution
 
Aleksandar BradarićSoftware DeveloperCommented:
I'm not sure I understand. Could you please explain... Do you need something like:
---
SELECT COUNT(*) as Week1Count FROM viHomeVisitsbyPara WHERE Week1=1 OR Week2=1 OR ... OR Week30=1
---

Some sample table data with a couple of rows and the expected result would be great :)
0
 
hbojorquezgeekAuthor Commented:
The expected resut would be something like

ParaID  | Week1Count  |    Week2Count  |    Week3Count  |
     3      |      2              |             1          |           1              |
     2      |       4             |              1          |           0              |

The table data would be
ParaID   | Week1  |    Week2    |   Week3  |
 3          |  1           |   2           |    3          |
 3          |  1           |    Null        |    Null     |
 2          |  1           |   2           |    Null       |
 2          |  1           |    Null        |    Null      |
 2          |  1           |    Null        |    Null      |
 2          |  1           |    Null        |    Null      |
 

As you can see I want something that summarizes wether a specific number is recorded  under each week... a 1 for Week1, 2 for Week2, etc...

0
 
steelseth12Commented:
SELECT ParaID,COUNT(Week1) as Week1Count, COUNT(Week2) as Week2Count,COUNT(Week3) as Week3Count FROM viHomeVisitsbyPara WHERE Week1=1 OR Week2=2 OR Week3=3 GROUP BY ParaID
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
hbojorquezgeekAuthor Commented:
Steelseth12...
nope..
That returned completely wrong tallies.
It returned
ParaID  | Week1Count  |    Week2Count  |    Week3Count   |
     3      |      2              |             2            |           2              |
     2      |       4             |             4            |           4              |
0
 
Aleksandar BradarićSoftware DeveloperCommented:
I see :) It's quite simple. Try this:
---
SELECT
  ParaID,
  COUNT(Week1) as Week1Count,
  COUNT(Week2) as Week2Count,
  COUNT(Week3) as Week3Count
FROM
  viHomeVisitsbyPara v
GROUP BY
  ParaID
---
0
 
Aleksandar BradarićSoftware DeveloperCommented:
By the way, there will have to be the NULL values in there for this to work. Here's a version that works with both NULLs and zeros:
---
SELECT
  ParaID,
  SUM(IF(Week1 IS NULL, 0, IF(Week1 = 1, 1, 0))) as Week1Count,
  SUM(IF(Week2 IS NULL, 0, IF(Week2 = 2, 1, 0))) as Week2Count,
  SUM(IF(Week3 IS NULL, 0, IF(Week3 = 3, 1, 0))) as Week3Count
FROM
  viHomeVisitsbyPara v
GROUP BY
  ParaID
---
0
 
steelseth12Commented:
The query i gave above ...

>> SELECT ParaID,COUNT(Week1) as Week1Count, COUNT(Week2) as Week2Count,COUNT(Week3) as Week3Count FROM viHomeVisitsbyPara WHERE Week1=1 OR Week2=2 OR Week3=3 GROUP BY ParaID

on the table data you gave

>>

ParaID   | Week1  |    Week2    |   Week3  |
 3          |  1           |   2           |    3          |
 3          |  1           |    Null        |    Null     |
 2          |  1           |   2           |    Null       |
 2          |  1           |    Null        |    Null      |
 2          |  1           |    Null        |    Null      |
 2          |  1           |    Null        |    Null      |
 

RETURNS

ParaID  | Week1Count  |    Week2Count  |    Week3Count  |
     2      |       4             |              1          |           0              |
     3      |      2              |             1          |           1              |

which is what you say you  want.
   
0
 
hbojorquezgeekAuthor Commented:
leannon's answer worked
... but I have a question..
Why are the argument of the second IF statement   --- 1,1,0?

  SUM(IF(Week1 IS NULL, 0, IF(Week1 = 1, 1, 0))) as Week1Count,
 
0
 
Aleksandar BradarićSoftware DeveloperCommented:
> SUM(IF(Week1 IS NULL, 0, IF(Week1 = 1, 1, 0))) as Week1Count,

The `IF(Week1 = 1, 1, 0)` translates to: `if the value for Week1 equals 1, then add 1 to the sum/count, else add 0`. It makes the query count only the items where `Week1 = 0`.

You can also modify it to say: `IF(Week1 > 0, 1, 0)`, which would count any non-zero number in the `Week1` field.
0
 
Aleksandar BradarićSoftware DeveloperCommented:
> It makes the query count only the items where `Week1 = 0`.

This should say: `It makes the query count only the items where `Week1 = 1`.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now