Solved

mysql discount and count to be used in same query

Posted on 2012-03-24
2
494 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

707 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