# Obtaining the total number of items in a column by the item's first letter.

Printable View

• October 9th, 06:09 PM
Daz
Obtaining the total number of items in a column by the item's first letter.
Hi everyone.

I have a list of books in a table (pp_books) with 3 columns (book_id,
book_name, book_type).

I am trying to construct a single query that will give me a total of
each of the books by there starting letter. For example, how many books
there are beginning with 'a', how many beginning with 'b' etc etc...
I need to add a lot more to the query, but I just need to try and
figure out this initial query as I am certain it can be done, it's just
a question of 'how'.

Any ideas would be appraciated.

Best regards.

Daz.

• October 9th, 06:21 PM
Brian
Re: Obtaining the total number of items in a column by the item's first letter.
Daz wrote:

SELECT SUBSTRING(book_name,1,1) AS letter,COUNT(*) as total FROM pp_books
WHERE 1 GROUP BY letter ORDER BY letter ASC;

--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png
• October 9th, 06:24 PM
Daz
Re: Obtaining the total number of items in a column by the item's first letter.

Brian Wakem wrote:
>
>
>
> SELECT SUBSTRING(book_name,1,1) AS letter,COUNT(*) as total FROM pp_books
> WHERE 1 GROUP BY letter ORDER BY letter ASC;
>
>
>
> --
> Brian Wakem
> Email: http://homepage.ntlworld.com/b.wakem/myemail.png[/ref]

Utterly ingenious! Thanks Brian. I spent the best part of the day
trying to figure it out, and just knew there was a simple solution.

• October 9th, 06:29 PM
strawberry
Re: Obtaining the total number of items in a column by the item's first letter.

Daz wrote:

untestes, but it's got to be something like this...

SELECT distinct(substr(pp1.book_name,1,1)) initial,count(pp2.book_name)
total FROM pp_books pp1
LEFT JOIN pp_books pp2 ON pp2.book_id = pp1.book_id
GROUP BY initial;

• October 9th, 06:33 PM
strawberry
Re: Obtaining the total number of items in a column by the item's first letter.

Brian Wakem wrote:
>
>
>
> SELECT SUBSTRING(book_name,1,1) AS letter,COUNT(*) as total FROM pp_books
> WHERE 1 GROUP BY letter ORDER BY letter ASC;
>
>
>
> --
> Brian Wakem
> Email: http://homepage.ntlworld.com/b.wakem/myemail.png[/ref]

oh, I guess that'll work too ;-)

• October 9th, 06:34 PM
Daz
Re: Obtaining the total number of items in a column by the item's first letter.

Brian Wakem wrote:
>
>
>
> SELECT SUBSTRING(book_name,1,1) AS letter,COUNT(*) as total FROM pp_books
> WHERE 1 GROUP BY letter ORDER BY letter ASC;
>
>
>
> --
> Brian Wakem
> Email: http://homepage.ntlworld.com/b.wakem/myemail.png[/ref]

Ugh... I am stuck again already.

I would like to add another column, this one should could the books
where the column type is equal to '1'. I will also need another 4
columns each using a different type (1-5). I tried this for 1 column...

SELECT LEFT( book_name, 1 ) AS letter, COUNT( * ) AS total, COUNT(
SELECT WHERE book_type = '1' ) AS retired
FROM pp_books
WHERE 1
GROUP BY letter
ORDER BY letter ASC
LIMIT 0 , 30;

Once again, I know there is a simple solution, I just can't figure it
out. I have spent a lot of today reading various advanced mysql query
tutorials, too. I have learnt tons, but evidently not what I needed to.

• October 9th, 06:38 PM
Daz
Re: Obtaining the total number of items in a column by the item's first letter.

strawberry wrote:
> >
> >
> >
> > SELECT SUBSTRING(book_name,1,1) AS letter,COUNT(*) as total FROM pp_books
> > WHERE 1 GROUP BY letter ORDER BY letter ASC;
> >
> >
> >
> > --
> > Brian Wakem
> > Email: http://homepage.ntlworld.com/b.wakem/myemail.png[/ref]
>
> oh, I guess that'll work too ;-)[/ref]

Hehe. Thanks strawberry. Your suggestion had been duly noted, as I know
there's usually more than a single correct way to do things. :)

• October 9th, 06:50 PM
Brian
Re: Obtaining the total number of items in a column by the item's first letter.
Daz wrote:

>>
>>
>>
>> SELECT SUBSTRING(book_name,1,1) AS letter,COUNT(*) as total FROM pp_books
>> WHERE 1 GROUP BY letter ORDER BY letter ASC;
>>
>>
>>
>> --
>> Brian Wakem
>> Email: http://homepage.ntlworld.com/b.wakem/myemail.png[/ref]
>
> Ugh... I am stuck again already.
>
> I would like to add another column, this one should could the books
> where the column type is equal to '1'. I will also need another 4
> columns each using a different type (1-5). I tried this for 1 column...
>
> SELECT LEFT( book_name, 1 ) AS letter, COUNT( * ) AS total, COUNT(
> SELECT WHERE book_type = '1' ) AS retired
> FROM pp_books
> WHERE 1
> GROUP BY letter
> ORDER BY letter ASC
> LIMIT 0 , 30;[/ref]

Not entirely sure what you are trying to do here. Something like:

SELECT SUBSTRING(book_name,1,1) AS letter,book_type,COUNT(*) as total FROM
pp_books WHERE 1 GROUP BY letter,book_type ORDER BY letter ASC

?

--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png
• October 9th, 07:01 PM
Daz
Re: Obtaining the total number of items in a column by the item's first letter.

Brian Wakem wrote:
> >
> > Ugh... I am stuck again already.
> >
> > I would like to add another column, this one should could the books
> > where the column type is equal to '1'. I will also need another 4
> > columns each using a different type (1-5). I tried this for 1 column...
> >
> > SELECT LEFT( book_name, 1 ) AS letter, COUNT( * ) AS total, COUNT(
> > SELECT WHERE book_type = '1' ) AS retired
> > FROM pp_books
> > WHERE 1
> > GROUP BY letter
> > ORDER BY letter ASC
> > LIMIT 0 , 30;[/ref]
>
>
> Not entirely sure what you are trying to do here. Something like:
>
> SELECT SUBSTRING(book_name,1,1) AS letter,book_type,COUNT(*) as total FROM
> pp_books WHERE 1 GROUP BY letter,book_type ORDER BY letter ASC
>
> ?
>
> --
> Brian Wakem
> Email: http://homepage.ntlworld.com/b.wakem/myemail.png[/ref]

Hi Brian.

Sorry :(

What I am looking for is something that will output a table a lot like
this:

+--------+-------+--------+--------+--------+--------+--------+
| letter | total | type_1 | type_2 | type_3 | type_4 | type_5 |
+--------+-------+--------+--------+--------+--------+--------+
| A | 10 | 4 | 0 | 3 | 1 | 2 |
+--------+-------+--------+--------+--------+--------+--------+
| B | 28 | 19 | 1 | 4 | 1 | 3 |

• October 9th, 07:08 PM
Daz
Re: Obtaining the total number of items in a column by the item's first letter.

Daz wrote:
> >
> >
> > Not entirely sure what you are trying to do here. Something like:
> >
> > SELECT SUBSTRING(book_name,1,1) AS letter,book_type,COUNT(*) as total FROM
> > pp_books WHERE 1 GROUP BY letter,book_type ORDER BY letter ASC
> >
> > ?
> >
> > --
> > Brian Wakem
> > Email: http://homepage.ntlworld.com/b.wakem/myemail.png[/ref]
>
> Hi Brian.
>
> Sorry :(
>
> What I am looking for is something that will output a table a lot like
> this:
>
> +--------+-------+--------+--------+--------+--------+--------+
> | letter | total | type_1 | type_2 | type_3 | type_4 | type_5 |
> +--------+-------+--------+--------+--------+--------+--------+
> | A | 10 | 4 | 0 | 3 | 1 | 2 |
> +--------+-------+--------+--------+--------+--------+--------+
> | B | 28 | 19 | 1 | 4 | 1 | 3 |[/ref]

Using WHERE I don't seem to have any problems filtering the results.
It's just getting the right results into the right columns, I am
struggling with. :)

• October 9th, 07:26 PM
Luuk
Re: Obtaining the total number of items in a column by the item's first letter.

"Daz" <com> schreef in bericht
news:googlegroups.com...
>>
>> Hi Brian.
>>
>> Sorry :(
>>
>> What I am looking for is something that will output a table a lot like
>> this:
>>
>> +--------+-------+--------+--------+--------+--------+--------+
>> | letter | total | type_1 | type_2 | type_3 | type_4 | type_5 |
>> +--------+-------+--------+--------+--------+--------+--------+
>> | A | 10 | 4 | 0 | 3 | 1 | 2 |
>> +--------+-------+--------+--------+--------+--------+--------+
>> | B | 28 | 19 | 1 | 4 | 1 | 3 |[/ref]
>
> Using WHERE I don't seem to have any problems filtering the results.
> It's just getting the right results into the right columns, I am
> struggling with. :)
>[/ref]

try:
SELECT LEFT( book_name, 1 ) AS letter, COUNT( * ) AS total, SUM(IF(
book_type = '1',1,0) ) AS retired
FROM pp_books
WHERE 1
GROUP BY letter
ORDER BY letter ASC
LIMIT 0 , 30;

suc6

• October 9th, 07:30 PM
Daz
Re: Obtaining the total number of items in a column by the item's first letter.

Luuk wrote:
> >
> > Using WHERE I don't seem to have any problems filtering the results.
> > It's just getting the right results into the right columns, I am
> > struggling with. :)
> >[/ref]
>
>
> try:
> SELECT LEFT( book_name, 1 ) AS letter, COUNT( * ) AS total, SUM(IF(
> book_type = '1',1,0) ) AS retired
> FROM pp_books
> WHERE 1
> GROUP BY letter
> ORDER BY letter ASC
> LIMIT 0 , 30;
>
>
> suc6[/ref]
It works beautifully! Thanks Luuk. :)