Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 22nd, 2006, 10:24 AM
Registered User
 
Join Date: Nov 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default advance count (2 counts in a row)

hello

i have a table like this :

id , city , type
1 NY X
2 NY X
3 NY Y
4 LA X
5 LA Y
6 LA Y
7 LA Y
8 LA Y
9 BA X
9 BA Y

i need to build a quary that will return the type group by city

city X Y
NY 2 1
LA 1 4
BA 1 1

i cannot find how to build this quary

can anuone help me ?
 
Old November 22nd, 2006, 11:30 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

i there.. i see a little problem with the query you are trying to build.. what if you have more variables (like X, Y, Z, W)...if that posible???

HTH

Gonzalo
 
Old November 22nd, 2006, 11:34 AM
Registered User
 
Join Date: Nov 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

it dosent metter, I need to return only the count for X or Y
 
Old November 23rd, 2006, 05:04 AM
Authorized User
 
Join Date: Mar 2006
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Select
       city,
       count (case type = 'X' then 1 else null end) as X,
       count (case type = 'Y' then 1 else null end) as Y
from table 1
group by city

something like this should work







Similar Threads
Thread Thread Starter Forum Replies Last Post
SQLDataSource row count areid ASP.NET 2.0 Basics 3 June 5th, 2010 06:02 AM
Row Count in each table ayazhoda Access VBA 0 April 15th, 2008 04:31 PM
Row count Discrepancy bph SQL Server 2000 9 January 26th, 2006 02:04 PM
SELECT COUNT as a ROW?? t0ny SQL Language 3 December 8th, 2004 05:31 AM
Row Count Walden SQL Server 2000 6 June 9th, 2003 12:47 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.