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

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

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
DPP2011
Asked:
DPP2011
  • 2
1 Solution
 
TommySzalapskiCommented:
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
 
TommySzalapskiCommented:
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
 
DPP2011Author Commented:
Thank you,  Solved the problem.
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.

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