Solved

mysql discount and count to be used in same query

Posted on 2012-03-24
2
450 Views
Last Modified: 2012-04-04
i have this query

select distinct(origin) from names order by id desc

now, i want to apply the count also to this query to actually count the pictures in the query

the pictures table is different and origin is the field which matches in names table and the pictures table

so how can i do it

any idea
0
Comment
2 Comments
 
LVL 7

Expert Comment

by:designatedinitializer
ID: 37760769
select COUNT(*), distinct(origin) from names order by id desc
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 37761947
select origin, count(id) from names group by origin;

Note that you cannot really effectively order by a column outside origin and count(id) because you already aggregated rows here.  Now if you want to join it with data with you pictures table you need something like:

select p.column1, count(n.id) from pictures p join names n on p.origin=n.origin group by p.column1

if you need to add more columns, just make sure you add them to both the select and group by clauses.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now