Solved

arrange my sql data

Posted on 2013-01-25
4
250 Views
Last Modified: 2014-05-19
I am creating a report to show the break down of people symptoms. The symptom field is comma separated.  I have tried quit a few things with  sql but I can't get the data the way I want it.
 
                             Table A
symptom                                  ID
Cold,Flu,Pink Eye                       18
Cold                                           22
Flu,Bloody Nose                        23
Flu, Pink Eye, Bloody Nose        24

What I want to get is the count of how many people have colds and their ID, how many people have Flu and their ID  like
Cold 2 18,22
Flu 3 18,23,24
Is their a way of doing this?
0
Comment
Question by:spectrumcare
  • 2
4 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 38821767
Your main problem is the way your data is stored - it is hard to generate reports the way it was done.  It should have been stored liked this:

symptom                                  ID
Cold                                         18
Flu                                            18
Pink Eye                                    18
Cold                                           22
Flu                                        23    
Bloody Nose                        23
Flu                                        24
Pink Eye                               24
Bloody Nose                        24

The above is the standard way to store data with many-to-many relationship.  If you had it done that way you could have done this:

select symptom, count(id), group_concat(id) from table_name group by symptom;

Rather than developing a complicated workaround I suggest you focus on fixing your data structure - it will solve many other things along the way.
0
 

Author Comment

by:spectrumcare
ID: 38827022
I do agree but this data is being stored is coming from a text area box. The user types in the symptom and they can edit this when they want.  To do a database change for 1 report is not worth changing multiple pages for it. I did however come up with a way of doing it on the week end.  It is probably not the best way of doing it but it is a way.
I loop my query getting the symptoms splitting the sting and putting the values into an array. I then loop the array querying the original query to find the symptom that matches. The record count of the query gives me how many people have the symptom and I have their ID's to get their name.
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 38829625
So what happens if the user forgets to separate symptoms with a comma (I assume that's the delimiter you use)?  Your report might contain something like:

Pink Eye     5     10,11,12,13,14
Pink Eye Bloody Nose     1      15
Bloody Nose      3      16,17,18

If the client can live with that, then fine.  But if I foresee more complicated requirements later on I would recommend revisiting the database design.
0
 
LVL 3

Expert Comment

by:stevejacob68
ID: 40076806
Hi,
Try this statement:
Select * from table_name where symptom=”cold” or symptom=”flu” or symptom=”pink” or symptom=”eye”
And write other statement like this to generate other output
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

759 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

23 Experts available now in Live!

Get 1:1 Help Now