This

select _fkProjects, _PNI, _Result, _Analysis, count(*)
from callstats
group by _fkProjects, _PNI, _Result, _Analysis

produces a nice list with the totals for _fkProjects, _PNI, _Result, _Analysis, I'd like to get totalling at each stage eg

_fkProjects, _PNI, _Result
_fkProjects, _PNI
_fkProjects

and a grand total

Guru wanted.

Roy Lambert

Roy,

<< I'd like to get totalling at each stage>>

Try something like this.

SELECT Level1, Level2, Level3, Count(*)
  FROM TableA
  GROUP BY Level1, Level2, Level3   

UNION

SELECT Level1, Level2, '', Count(*)
  FROM TableA
  GROUP BY Level1, Level2
                         
UNION

SELECT Level1, '', '', Count(*)
  FROM TableA
  GROUP BY Level1

Richard Harding

Richard


Near, but I get

grandtotal
group total
line total
group total
line total

I tried an order by clause but no go.

Roy Lambert

Roy

> Near, but I get
>
> grandtotal
> group total
> line total
> group total
> line total
>
> I tried an order by clause but no go.

Modifying Richards code slightly to use a derived table should work

SELECT * FROM (
SELECT Level1, Level2, Level3, Count(*)
  FROM TableA
  GROUP BY Level1, Level2, Level3
UNION
SELECT Level1, Level2, '', Count(*)
  FROM TableA
  GROUP BY Level1, Level2
UNION
SELECT Level1, '', '', Count(*)
  FROM TableA
  GROUP BY Level1
UNION
SELECT 'Grand Total' as Level1, '', '', Count(*)
  FROM TableA
  GROUP BY Level1
) t1
ORDER BY if(t1.level1 = 'Grand Total','zzzzzz',t1.level1),if(t1.level2 =
'','zzzzzy',t1.level2),if(t1.level3 = '','zzzzzx',t1.level3)

John

John


I'm still massaging it, but its looking good. However, I do have to say this is where I miss PICK. You would have thought, given SQLs beginnings as a reporting tool it would be better at this.

Roy Lambert

I've given in. I can get near but not there. All of the examples I can find on the web go 1 subtotal and a grand total. Nothing like the 3 layers of subtotals plus grand total that I need. I'll use a mixture of SQL and Delphi.

Roy Lambert

Roy

> I've given in. I can get near but not there. All of the examples I can
find on the web go 1 subtotal and a grand total. Nothing like the 3 layers
of subtotals plus grand total that I need. I'll use a mixture of SQL and
Delphi.

What was wrong with the code posted?  I just tried it here and it seems to
work fine.

John

John


This is where I ended up

SELECT _Caption, _Level, _PNI,_Result,_Analysis,_Counts FROM
(
SELECT
'' AS _Caption,
_PNI,
IF(_fkCallMenu > 0 then _fkCallMenu + 0.3,_fkCallMenu - 0.3) AS _Level,
COALESCE(_Result,'') AS _Result,
COALESCE(_Analysis,'') AS _Analysis,
Count(*) AS _Counts
FROM CallStats                
WHERE
_fkProjects = 189
GROUP BY _Level, _PNI, COALESCE(_Result,''), COALESCE(_Analysis,'')

UNION

SELECT
'',
_PNI,
IF(_fkCallMenu > 0 then _fkCallMenu + 0.2,_fkCallMenu - 0.2) AS _Level,
COALESCE(_Result,'') AS _Result,
'',
Count(*) AS _Counts
FROM CallStats                
WHERE
_fkProjects = 189
GROUP BY _Level, _PNI, COALESCE(_Result,'')

UNION

SELECT
_Caption,
_PNI,
_fkCallMenu AS _Level,
'',
'',
Count(*) AS _Counts
FROM CallStats
JOIN CallMenu ON _fkCallMenu = CallMenu._ID
WHERE
_fkProjects = 189
GROUP BY _Level, _PNI
   
UNION

SELECT                           
'',
'X',
0,
'',
'',
Count(*) AS _Counts
FROM
CallStats                
WHERE
_fkProjects = 189
) Stats
ORDER BY Stats._Level DESC

Which gets very near for a specific project, add the project layer in and its again I can get very nearly there. Then I start thinking about presentation and as is its horrible (I'm being kind so there's a lot of work to get it looking good. Combine the two and maintainability is starting to slip. Hence a much simpler query plus a nice "little" loop in Delphi.

Roy Lambert

How about a stored procedure that dumps the results to a table set aside
specifically for this kind of reporting?

I've done that before and it works well.  I usually create a GUID or
something from Delphi and pass it in as a parameter to add in every row and
then do the report based on that GUID.  That prevents records from getting
mixed up with others in the database.

--
David Cornelius
Cornelius Concepts

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:3840B5D7-DCC0-48AA-81D6-60C7299E7D7A@news.elevatesoft.com...
> John
>
>
> This is where I ended up
>
> SELECT _Caption, _Level, _PNI,_Result,_Analysis,_Counts FROM
> (
> SELECT
> '' AS _Caption,
> _PNI,
> IF(_fkCallMenu > 0 then _fkCallMenu + 0.3,_fkCallMenu - 0.3) AS _Level,
> COALESCE(_Result,'') AS _Result,
> COALESCE(_Analysis,'') AS _Analysis,
> Count(*) AS _Counts
> FROM CallStats
> WHERE
> _fkProjects = 189
> GROUP BY _Level, _PNI, COALESCE(_Result,''), COALESCE(_Analysis,'')
>
> UNION
>
> SELECT
> '',
> _PNI,
> IF(_fkCallMenu > 0 then _fkCallMenu + 0.2,_fkCallMenu - 0.2) AS _Level,
> COALESCE(_Result,'') AS _Result,
> '',
> Count(*) AS _Counts
> FROM CallStats
> WHERE
> _fkProjects = 189
> GROUP BY _Level, _PNI, COALESCE(_Result,'')
>
> UNION
>
> SELECT
> _Caption,
> _PNI,
> _fkCallMenu AS _Level,
> '',
> '',
> Count(*) AS _Counts
> FROM CallStats
> JOIN CallMenu ON _fkCallMenu = CallMenu._ID
> WHERE
> _fkProjects = 189
> GROUP BY _Level, _PNI
>
> UNION
>
> SELECT
> '',
> 'X',
> 0,
> '',
> '',
> Count(*) AS _Counts
> FROM
> CallStats
> WHERE
> _fkProjects = 189
> ) Stats
> ORDER BY Stats._Level DESC
>
> Which gets very near for a specific project, add the project layer in and
> its again I can get very nearly there. Then I start thinking about
> presentation and as is its horrible (I'm being kind so there's a lot
> of work to get it looking good. Combine the two and maintainability is
> starting to slip. Hence a much simpler query plus a nice "little" loop in
> Delphi.
>
> Roy Lambert
>

Roy

> Which gets very near for a specific project, add the project layer in and
its again I can get very nearly there. Then I start thinking about
presentation and as is its horrible (I'm being kind so there's a lot of
work to get it looking good. Combine the two and maintainability is starting
to slip. Hence a much simpler query plus a nice "little" loop in Delphi.

I think this is a better approach.  If it's a report the engine usually does
sub/grand totals well.  If it's a grid I use express grids and they support
sub/grand totalling.

John