HI,
I have a table that holds printing information (clientcode, Username, PapaerSize, Pages, DatePrinted).I would like to sumerise it as follows:
Client Code Username A4 Total Letter Total
Building karbell 1 1
Building simhowa 4
Building simhowa 1
Communications elikirk 2
Communications kendyer 3
Communications raebarl 6
Communications suewall 3
Communications suewall 1

I have the following script that displays the data as follows

SELECT ClientCode, SUBSTRING(Username, 10, 10) AS Username, PaperSize, COUNT(Pages) AS Pages
FROM tbl_pcounterlog
GROUP BY ClientCode, SUBSTRING(Username, 10, 10), PaperSize
HAVING (NOT (ClientCode IS NULL))

Client Code Username PaperSize Pages
Building karbell A4 1
Building simhowa A4 4
Building simhowa Letter 1
Communications elikirk Letter 2
Communications kendyer A4 3
Communications raebarl A4 6
Communications suewall A4 3
Communications suewall Letter 1

Can anyone point me in the right direction.

Thank you

Greg

For client code,username and paparsize wise use following

SELECT CLIENTCODE,USERNAME,
SUM(CASE WHEN PAPERSIZE='A4' THEN PAGES END) A4TOTAL,
SUM(CASE WHEN PAPERSIZE='LETTER' THEN PAGES END) LETTERTOTAL
FROM TBL_PCOUNTERLOG A
GROUP BY CLIENTCODE,USERNAME,PAPERSIZE


For clientcode,username wise summary (all will come in one line) use following
SELECT CLIENTCODE,USERNAME,
SUM(CASE WHEN PAPERSIZE='A4' THEN PAGES END) A4TOTAL,
SUM(CASE WHEN PAPERSIZE='LETTER' THEN PAGES END) LETTERTOTAL
FROM TBL_PCOUNTERLOG A
GROUP BY CLIENTCODE,USERNAME


You can add as many cases according to you available paper size.



urt

Thanks urt this has worked nicely.I have another sinario. I would like to add a field based on the value of another. The field that I would like to creat is not held in the DB. essentially this is what I want to do

ClientCode Directorate
Building Corporate Services
Policy Strategic planning

this is the statement that I am using - it works but I was wondering if there is a better way of writing it

SELECT

SUBSTRING(Username, 10, 10) AS Username,
SUBSTRING(Printer, 16, 40) AS Printer,
CONVERT(varchar(10), DatePrinted, 103) AS DatePrinted,
ClientCode AS ClientCode,
PaperSize AS PaperSize,
Pages AS Pages,
Cost AS Cost,
case when clientcode = 'Finance' then 'Corporate Services'
when clientcode = 'Information Management Services ' then 'Corporate Services'
when clientcode = 'Customer Services' then ' Corporate Services'
when clientcode = 'Committee Support Group' then 'Corporate Services'
when clientcode = 'network Infrastructure' then 'Operations and services'
when clientcode = 'Community Services' then 'Operationd and Services'
when clientcode = 'Policy' then 'Strategic Planning and Policy'
when clientcode = 'Legal' then 'Strategic Planning and Policy'
when clientcode = 'Regulatory' then 'Regulatory'
when clientcode = 'Building' then 'Regulatory'
when clientcode = 'Communications' then 'Chief Executive'
when clientcode = 'Human Resources' then 'Chief Executive'
when clientcode = 'Executives' then 'Chief Executive' end AS Directorate
FROM tbl_pcounterlog

WHERE (NOT (ClientCode IS NULL))

ORDER BY CONVERT(varchar(10), DatePrinted, 103)

Thanks Greg

Their is another way, but it will not work instantly, because for that, you have to change your database design and it will take some time. So right now, method that you are using, is the best one.

urt