how to segregate values from one column into 2 columns as table

Journal_T - Basically this is the journal table with positive and negative values. Fields are: id, date, ref, description, amount, segment, period, year.

I want to display this in two separate columns - debits (containing positive values) and credits (containing negative values). Following is the query I am using:

$qry = mysql_query("SELECT id, date, ref, description, SUM(amount), 
                           segment, period, year
开发者_StackOverflow中文版                      FROM Journal_T 
                  GROUP BY segment, year, period, id") or die(mysql_error());

@Michael is right in that this kind of problems is commonly solved with the help of CASE. Yet I think he's got it wrong, and the CASEs should indeed be inside the aggregate functions, like @OMG Ponies has correctly pointed out. Here:

SELECT
  id,
  date,
  ref,
  description,
  SUM(CASE WHEN amount > 0 THEN Amount END) AS TotalPositive, 
  SUM(CASE WHEN amount < 0 THEN Amount END) AS TotalNegative, 
  segment,
  period,
  year
FROM Journal_T 
GROUP BY segment, year, period, id

On a different point, I don't think that the values of date, ref and description have much sense in the output for this query as long as you do not include them in the GROUP BY list. MySQL does allow you to include them into the SELECT list without aggregating, but the values returned for those columns might be arbitrary and not very much related to the other data retrieved, particularly to the sums.

If I understand the schema, do it with a CASE:

SELECT id,
  date,
  ref,
  description,
  CASE WHEN SUM(amount) >= 0 THEN SUM(amount) ELSE NULL END AS positive,
  CASE WHEN SUM(amount) < 0 THEN SUM(amount) ELSE NULL END AS negative,
 ,segment,period,year FROM Journal_T GROUP BY segment,year,period,id