2. PHPRunner General questions
  3. SQL query help
This topic is locked

SQL query help

 		2/3/2008 2:16:25 PM
PHPRunner General questions
D
 dmcconnell author

Is it possible to combine the following three SQL queries into one that can be used in the PHPRunner report SQL query?
select `id`,

`type`,

`site`,

`division`,

`costcenter`,

`floor`,

`location`,

`user`,

`model`,

`serial`,

`purchasedate`,

`other`,

`notes`

From `hardware`
select count() as laptop_count from hardware where type = 'Laptop'
select count() as desktop_count from hardware where type = 'Desktop'
Thanks,

David
A
 		alang 2/3/2008

Yes probably, but how about creating additional columns in the SQL which you can then count in your report:
select `id`,

`type`,

`site`,

...
(type='Laptop') AS LaptopType,

(type='Desktop') AS DesktopType
From `hardware`
The extra columns will show as 1 or 0 depending on the type for each row. The advantage of this way is that if you restrict your main view of the table by cost centre or division etc, your report will end up with the right totals.