Count from a boolean field

Dev Shed Forums Sponsor:

November 21st, 2012, 05:45 AM

raven3k

Registered User

Join Date: Nov 2012

Posts: 2

Time spent in forums: 2 h 53 m 48 sec
Reputation Power: 0

Count from a boolean field

a short schema of the database

Code:

system
  pksystem
  ..and many information fields
user
  pkuser
  ..and many user fields
user_system
  pkuser_system
  fksystem
  fkuser
  admin (boolean 0 or 1)
  ...and many more

My problem is now, i will see all Systems from the user_system Table where are the count of admins is not higher 2

the first syntax from me

Code:

SELECT * 
FROM 
user_system us
LEFT JOIN 
system s 
ON us.fksystem = s.pksystem
GROUP BY 
us.fksystem,
admin 
HAVING 
count(us.admin) < 2 
ORDER BY 
admin

UPDATE other SQL Statement (maybe this is correct)

Code:

SELECT
 fksystem,
sum(admin) sum_admin 
FROM 
`user_system` 
GROUP BY 
fksystem 
HAVING 
sum_admin < 2 
ORDER BY 
sum_admin

Last edited by Guelphdad : November 21st, 2012 at 01:36 PM. Reason: replaced mysql tags with code tags

November 21st, 2012, 11:47 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,369

Time spent in forums: 3 Months 1 Week 2 Days 6 h 14 m 57 sec
Reputation Power: 4140

the second query is correct --

Code:

SELECT fksystem
     , SUM(admin) AS sum_admin 
  FROM user_system
GROUP 
    BY fksystem 
HAVING sum_admin < 2 
ORDER 
    BY sum_admin

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

November 22nd, 2012, 12:56 AM

raven3k

Registered User

Join Date: Nov 2012

Posts: 2

Time spent in forums: 2 h 53 m 48 sec
Reputation Power: 0

Quote:

Originally Posted by r937

the second query is correct --

Code:

SELECT fksystem
     , SUM(admin) AS sum_admin 
  FROM user_system
GROUP 
    BY fksystem 
HAVING sum_admin < 2 
ORDER 
    BY sum_admin

thanks for your opinion...use now this sql query without scruple

Viewing: Dev Shed Forums > Databases > MySQL Help > Count from a boolean field

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump