/    Sign up×
Community /Pin to ProfileBookmark

1 query 3 counts

Copy linkTweet thisAlerts:
Dec 05.2011

Hi,

I was wondering if it is possible to write a query that gets the result of 3 counts?

For example I have a table called “boxes” that has the following:

  • – id

  • – name

  • – color

    • now I want to write a query that gets the count of boxes but filter them based on color so if we have 30 red and 20 blue and 10 green

    it does not give me a result of 60 but rather it says
    red 30
    blue 20
    green 10

    Obviously it can easily be done with 3 queries, but can it be done by 1?

    to post a comment
    PHP

    4 Comments(s) ↴

    Copy linkTweet thisAlerts:
    @bionoidDec 05.2011 — Try this...

    [CODE]SELECT
    (SELECT COUNT(*) FROM boxes b WHERE b.color = "red") as red,
    (SELECT COUNT(*) FROM boxes b WHERE b.color = "blue") as blue,
    (SELECT COUNT(*) FROM boxes b WHERE b.color = "green") as green[/CODE]

    Should give you a table with three columns containing the count of each color.
    Copy linkTweet thisAlerts:
    @DasherDec 05.2011 — SELECT color, COUNT(color) FROM boxes GROUP by color
    Copy linkTweet thisAlerts:
    @eval_BadCode_Dec 05.2011 — SELECT color, COUNT(color) FROM boxes GROUP by color[/QUOTE]

    SELECT COALESCE(color,'invisible'), COUNT(COALESCE(color,'invisible)) FROM boxes GROUP BY COALESCE(color,'invisible');
    Copy linkTweet thisAlerts:
    @DasherDec 05.2011 — I get an error when I try and use COALESCE as shown. My query seems to work ok with MySQL 5.1.36, which is a little dated, but COALESCE has been in MySQL since 3.23.3. I am using it on real data, but modify the terms for my db and table.
    ×

    Success!

    Help @XeroSiS spread the word by sharing this article on Twitter...

    Tweet This
    Sign in
    Forgot password?
    Sign in with TwitchSign in with GithubCreate Account
    about: ({
    version: 0.1.9 — BETA 5.6,
    whats_new: community page,
    up_next: more Davinci•003 tasks,
    coming_soon: events calendar,
    social: @webDeveloperHQ
    });

    legal: ({
    terms: of use,
    privacy: policy
    });
    changelog: (
    version: 0.1.9,
    notes: added community page

    version: 0.1.8,
    notes: added Davinci•003

    version: 0.1.7,
    notes: upvote answers to bounties

    version: 0.1.6,
    notes: article editor refresh
    )...
    recent_tips: (
    tipper: @Yussuf4331,
    tipped: article
    amount: 1000 SATS,

    tipper: @darkwebsites540,
    tipped: article
    amount: 10 SATS,

    tipper: @Samric24,
    tipped: article
    amount: 1000 SATS,
    )...