| Author |
 Topic  |
|
|
Intrepidone
Average Member
Canada
515 Posts |
 Posted - 17 November 2005 : 06:53:48
|
I thought this would be easy but I am not having any luck,
I would like to do a count of all Laptops, Computers, Cell Phones that each Department has. My SQL right now will list the results like this
Sports 1 Laptop
Sports 3 Computer
Sports 1 Cell Phone
Clothing 2 Laptop
Clothing 5 Computer
Clothing 2 Cell Phone
"SELECT DISTINCT(tblProd.Department), Count(tblProd.EquipID) " & _
'"FROM tblProd " & _
'"WHERE (tblProd.EquipID='PC' AND tblProd.Model Like '%Computer%') " & _
'"OR (tblProd.EquipID='LT' AND tblProd.Model Like '%Laptop%') " & _
'"OR (tblProd.EquipID='CP' AND tblProd.Model Like '%Cell Phone%') " & _
'"GROUP BY tblProd.Department, tblProd.EquipID " & _
I would like the results displayed like this
Sports 1 3 1
Clothing 2 5 2
Any Ideas?
Thank You |
Intrepidone |
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 17 November 2005 : 07:42:19
|
you're trying to pivot your data, in effect. Either use Excel Pivot Tables for a quick-fix, or you'll have to create a #temp table and populate it from your query/code.
There is an option with SQL Server 2005 to TRANSPOSE results, which will do this as well, but you may not have 2005 yet |
 |
|
|
Intrepidone
Average Member
Canada
515 Posts |
Posted - 17 November 2005 : 08:06:20
|
Thanks pdrg, I just ran it through the wizard and got most the results I want but it is giving me 3 instances of each Department like this
Department Computer Laptop Cell Phone
Sports 1
Sports 3
Sports 1
Clothing 2
Clothing 5
Clothing 2
I would like to see
Department Computer Laptop Cell Phone
Sports 1 3 1
Clothing 2 5 2
Here is the SQL for the Pivot
strSQL="TRANSFORM Count(EquipID) " & _
"SELECT DISTINCT(tblProd.Department), Count(tblProd.EquipID) " & _
"FROM tblProd " & _
"WHERE (tblProd.EquipID='PC' AND tblProd.Model Like '%Computer%') " & _
"OR (tblProd.EquipID='LT' AND tblProd.Model Like '%Laptop%') " & _
"OR (tblProd.EquipID='CP' AND tblProd.Model Like '%Cell Phone%') " & _
"GROUP BY tblProd.Department, tblProd.EquipID " & _
"PIVOT tblProd.EquipID"
Any ideas of how to display the Department only once?
Thanks |
Intrepidone |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 17 November 2005 : 08:24:32
|
I'm not sure which db engine you're using for this, but excel pivots would give you the option to summaruse for each department if you used them. If this is for a web page, I'm not absolutely sure, I don't have a test setup any more. As your categories seem to be pretty well defined, you could build your #temp table and populate it, or load the TRANSFORMed data from your above post into some temp table and SUM from there.
Sorry I don't have an exact answer for you...I'll leave that to some sharper brains instead, unless genius strikes me! |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 17 November 2005 : 08:37:17
|
| Why not load it into a two-dimensional array and then write the display as desired instead of trying to do it all in one uber SQL statement? |
|
|
|
Intrepidone
Average Member
Canada
515 Posts |
Posted - 19 November 2005 : 14:31:24
|
I was finally able to produce the results I wanted after playing around with the Pivot
strSQL="TRANSFORM Count([Model]) AS CountOfModel " & _
"SELECT [Department] " & _
"FROM tblProd " & _
"WHERE (EquipID='PC' AND Model LIKE '%Computer%') Or (EquipID='LT' AND Model LIKE '%Laptop%') Or (EquipID='CP' AND Model LIKE '%Cell Phone%') " & _
"GROUP BY [Department] " & _
"PIVOT [EquipID]"
Here are the results that show up in my web page
Department: Computer Laptop Cell Phone
----------------------------------------------
Sports: 1 3 1
Clothing: 2 5 2
Children: 1 1 2
etc.....
It's been awhile since I have visited these forums, thanks for the input. |
Intrepidone |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 20 November 2005 : 12:48:10
|
| Glad you got it to work. Maybe its time for me to start looking into the PIVOT stuff a little more... |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 21 November 2005 : 07:10:15
|
Pivots are great, there's some good starter courses on Microsoft.com/office - try digging around a bit.
hth |
|
|
|
Intrepidone
Average Member
Canada
515 Posts |
Posted - 03 July 2007 : 22:39:07
|
It's been about 19 months since I posted this below, the Pivot table has worked great but now were running SQL Server 2005 and it doesn't work, does anyone know how I can get the following to work on SQL 2005. I have had no training on SQL 2005, they passed me the server CD and said here you go, I managed to get everything up and working except for the PIVOT.
Thanks again, still see alot of old nicks around 
quote:
strSQL="TRANSFORM Count([Model]) AS CountOfModel " & _
"SELECT [Department] " & _
"FROM tblProd " & _
"WHERE (EquipID='PC' AND Model LIKE '%Computer%') Or (EquipID='LT' AND Model LIKE '%Laptop%') Or (EquipID='CP' AND Model LIKE '%Cell Phone%') " & _
"GROUP BY [Department] " & _
"PIVOT [EquipID]"
Here are the results that show up in my web page
Department: Computer Laptop Cell Phone
----------------------------------------------
Sports: 1 3 1
Clothing: 2 5 2
Children: 1 1 2
|
Intrepidone |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 04 July 2007 : 08:45:03
|
| Hmmm sorry man, I'm waaay too rusty now :-$ Let one of the bright young things sieze the day |
|
|
|
Intrepidone
Average Member
Canada
515 Posts |
Posted - 04 July 2007 : 10:43:14
|
Thanks anyway pdrg, I have been searching for examples with SQL 2005 but I'am getting lost, little rusty here to  |
Intrepidone |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
|
|
Intrepidone
Average Member
Canada
515 Posts |
Posted - 05 July 2007 : 19:41:09
|
| Thanks HuwR, I've been reading up on it at the site you posted, getting 1/2 lost in trying to understand it but I'll work on it. |
Intrepidone |
|
|
| |
Topic |
|
Topic Locked
