Solved

Can anyone tell me how to do equations with concat_goup query MYSQL

Posted on 2011-02-15
3
347 Views
Last Modified: 2012-05-11
I have the follow query:

SELECT
ID,
sqldate,
sqlvalue,
CAST(GROUP_CONCAT(if(stat_number = 138, sqlvalue, NULL)) AS UNSIGNED) AS Total,
CAST(GROUP_CONCAT(if(stat_number = 144, sqlvalue, NULL)) AS UNSIGNED) AS Public,
FROM statval1
WHERE (DAYOFWEEK(sqldate) = 5)
GROUP BY sqldate
ORDER BY sqldate

for Table:
ID-   sql Date      - Stat_number - sqlvalue -
1 - 2002/12/5     -154           - 652
2- 2002/12/5      -138           - 547
3- 2002/12/5      -144           - 687
4- 2002/12/5      -195           - 687
5 - 2002/12/12   -138           - 652
6- 2002/12/12    -154           - 547
7- 2002/12/12    -195           - 687
8- 2002/12/12    -144           - 687

I need to be able to multiply by rows the `total ` * 100 divided by `Public` and the end result
to be entered into a new colum `result`. This is grouped by date.

The query result should look like this:
    sqldate          - total    -     public   -    result   -
 2002/12/5        - 547      -     687     -    79.62
 2002/12/12      - 652      -     687     -     94.90

The could be 20 fields with the same date but a stat number will only show up once
per date (no duplicate stat number on columns with the same date)

Can anyone complete my query to generate the result required?
0
Comment
Question by:DPP2011
  • 2
3 Comments
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 total points
Comment Utility
You can't refer to them as total and public in the same query. You could nest the query or retype the portions you have so far. Also, use 100.0 so that it automatically casts to a double.
SELECT
ID,
sqldate,
sqlvalue,
CAST(GROUP_CONCAT(if(stat_number = 138, sqlvalue, NULL)) AS UNSIGNED) AS Total,
CAST(GROUP_CONCAT(if(stat_number = 144, sqlvalue, NULL)) AS UNSIGNED) AS Public,
CAST(GROUP_CONCAT(if(stat_number = 138, sqlvalue, NULL)) AS UNSIGNED) * 100 /CAST(GROUP_CONCAT(if(stat_number = 144, sqlvalue, NULL)) AS UNSIGNED)  AS result,
FROM statval1
WHERE (DAYOFWEEK(sqldate) = 5)
GROUP BY sqldate
ORDER BY sqldate
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
The other option (nesting) would look like this (be careful since Public is a keyword)
SELECT
ID,
sqldate,
sqlvalue,
Total,
Public,
Total*100.0/Public AS result
FROM
(
SELECT
ID,
sqldate,
sqlvalue,
CAST(GROUP_CONCAT(if(stat_number = 138, sqlvalue, NULL)) AS UNSIGNED) AS Total,
CAST(GROUP_CONCAT(if(stat_number = 144, sqlvalue, NULL)) AS UNSIGNED) AS Public,
FROM statval1
) tbl
WHERE (DAYOFWEEK(sqldate) = 5)
GROUP BY sqldate
ORDER BY sqldate
0
 

Author Closing Comment

by:DPP2011
Comment Utility
Thank you,  Solved the problem.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Creating and Managing Databases with phpMyAdmin in cPanel.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now