db_select group by of union I'm quite new to drupal ad I've some problem with parsing a query throught the API:

    SELECT t.pg,p.classe, sum(t.punti) AS punti, sum(t.vinte) AS vinte, sum(t.perse) AS perse, (sum(t.vinte)+sum(t.perse)) AS giocate
    FROM(
        SELECT sid, team_member_a_1 AS pg, punti_team_a AS punti, 1 AS vinte, 0 AS perse
        FROM `webform_views_unusual_arena__risultati_6` AS t1
        WHERE tipologia_risultato = 0
            AND vincente = 0
        UNION
        SELECT sid, team_member_a_2 AS pg, punti_team_a AS punti, 1 AS vinte, 0 AS perse
        FROM `webform_views_unusual_arena__risultati_6` AS t1
        WHERE tipologia_risultato = 0
            AND vincente = 0
    ) AS t
    JOIN webform_views_i_miei_pg_1 AS p ON t.pg = p.nome_pg
    GROUP BY pg


But I can't find any documentation about a subquery or how to add field like "1 AS vinte"

EDIT
Thank's to Tenken this is the code I used.

$subquery_1 = db_select('webform_views_unusual_arena__risultati_6', 't')
    -&gt;condition('t.tipologia_risultato', 0, '=')
    -&gt;condition('t.vincente', 0,'=')
    -&gt;fields('t', array('sid'));
$subquery_1-&gt;addExpression('team_member_a_1', 'pg');
$subquery_1-&gt;addExpression('punti_team_a', 'punti');
$subquery_1-&gt;addExpression(1, 'vinte');
$subquery_1-&gt;addExpression(0, 'perse');
$subquery_2 = db_select('webform_views_unusual_arena__risultati_6', 't')
    -&gt;condition('t.tipologia_risultato', 0, '=')
    -&gt;condition('t.vincente', 0,'=')
    -&gt;fields('t', array('sid'));
$subquery_2-&gt;addExpression('team_member_a_2', 'pg');
$subquery_2-&gt;addExpression('punti_team_a', 'punti');
$subquery_2-&gt;addExpression(1, 'vinte');
$subquery_2-&gt;addExpression(0, 'perse');
$query = $subquery_1-&gt;union($subquery_2);

$query-&gt;addExpression('sum(vinte)', 'vinte');
$query-&gt;addExpression('sum(perse)', 'perse');
$query-&gt;addExpression('sum(punti)', 'punti');       
$query-&gt;groupBy('pg');
$query-&gt;orderBy('punti', 'DESC');


Hope it will help someone else
See this WEALTH OF DOCUMENTATION on Dynamic Queries in D7 ... it will answer EVERYTHING :) .... in all seriousness :).

To add SUM(blah) as foo you want to add an SQL Expression to your Select clause with addExpression().

To add Group by COLNAME you want to use groupBy() with your db_selection, see group by docs.